Forum Discussion
Toggle Day Counter
Hey Everyone!
I need some help. 😞 I am trying to track accumulative days a piece of equipment is not operating. I want this to be as automated as possible and would like maybe some sort of toggle function that I would switch on/off (or operating/not operating) and excel counts how many days the toggle was in the "off" position from "start date" that I would have manually entered and "Todays" Date. I feel like this is possible maybe with some kind of CountIF formula but I do not know how to make sure it accumulates all the times it was switched off or add together the durations of those "off" times and not restart the count every time the toggle switches.
To follow that, it would need to be able to be set back or have a spot where I can manually tell it when to start its "off" position counting just in case I am unable to go in on the exact day its out of commission and update the spreadsheet.
Any other ideas for this are more than welcome - I mainly need minimal input or data entry on my end and for it to accumulate ALL days the equipment was not operating. It doesn't have to look fancy or actually be a button or anything like that. I just need it to count. I plan on referencing the final count cell on another sheet that will be for presentation purposes.
Thank y'all so much for your help! ❤️
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.
10 Replies
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.
