Forum Discussion
Toggle Day Counter
TSawyer1226 So the only way to do this is with a macro. i created a sheet with checkboxes that can be clicked ON and OFF to signify the machine is ON or OFF. Each time it will log that time onto an ongoing list of ON and OFF times. I then have a table to calculate the total ON and total OFF times. Note that those total times are updated when a change triggers a new calculation so if the machine is presently OFF that formula updates the total OFF time based on the time right now. You can change the time format to be days instead (i.e. integer numbers would work to signify days). I am also attaching a drawing on how I added a 2nd rig so you can add the 3rd, 4th, etc...
Hope this helps you.
Speaking in the abstract, which is about all I can do at this point, I think what you're asking is fairly straightforward.
If you'd like something less abstract, it would help a lot (A LOT!) if you posted a copy, or some facsimile thereof, of the specific spreadsheet you're working with, showing how you have your data organized. Also let us know which version of Excel you're working with; there are some features in the newest release of Excel that might be relevant, so it would be good to know if that's what you have.
mathetes Awesome! that's good to hear. I have attached my file. I have a reference sheet where I picture this counter to be placed (highlighted in yellow for you) and I planned for the total count days to be referenced from there onto the two Data Base sheets as needed for Calendar and Operating Days. Those two pages are my data input pages. That's where 99% of my data is inputted. The other Rig specific sheets are linked to the data base pages and are meant to just be refreshed and printed only.
Any help or recommendations to rework the sheets as needed to make the counter work or formula's to work better I am willing to look into and see how it fits overall.
Thanks!
TSawyer1226 So the only way to do this is with a macro. i created a sheet with checkboxes that can be clicked ON and OFF to signify the machine is ON or OFF. Each time it will log that time onto an ongoing list of ON and OFF times. I then have a table to calculate the total ON and total OFF times. Note that those total times are updated when a change triggers a new calculation so if the machine is presently OFF that formula updates the total OFF time based on the time right now. You can change the time format to be days instead (i.e. integer numbers would work to signify days). I am also attaching a drawing on how I added a 2nd rig so you can add the 3rd, 4th, etc...
Hope this helps you.
mtarler I actually do have a small question (I know, that didn't take very long lol). Where would I go or what do I click to turn the macro off? In the case something gets decommissioned I would like for it just stop counting without me losing all the data it had recorded up to that point.
TSawyer1226 Just delete the checkbox. No checkbox, no call to the macro. Note that I only wrote 1 macro (to rule them all, lol). So the macro will get the NAME of the checkbox (you can find and change it in the box to the left of the equation editor box) calling it and use that to know which list to update. So if/when you copy the checkbox you much change that name and the CAPTION (the text next to the checkbox) or else when you click that checkbox it will add the timestamp on the wrong list.
mtarler YOU. ARE. AMAZING.
I believe this will work for what I need it to do. I will try it out and let you know if I have any issues. Thank you so much for your time and help on this!