Toggle Day Counter

[TSawyer1226]

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! <3

[#2]

@TSawyer1226 

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.

[#3]

@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!

[#4 · Accepted solution]

@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. 

[#5]

@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! 

[#6]

@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. 

[#7]

@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.

[#8]

@mtarler Perfect! it has been a short adventure lol but thank you again for all of your help.

[#9]

@TSawyer1226  Hello again.  I have updated your newest sheet with a couple tweaks I think you will like.  I deleted all the redundant buttons (nice macros btw).  I reduced to 1 button per row.  I 'hide' the timestamp behind the button and then add the difference between NOW and that timestamp whenever it is changed from Operating to Not Operating.  Note that it only updates the Operating Count (Hidden) when you click the button so I added a 'running clock' condition to the Operation Days column so if the button is 'Operating' it will update the 'Operating Days' status on each refresh (FYI - having many rows can cause it to lag as it has to update every cell in the row on every entry/refresh anywhere on the page).  I did not try to make the button different colors but the text does change :)

BTW - I responded here so that your new request can stay 'unanswered' and maybe get another solution from another volunteer.

best of luck.

[#10]

@mtarler  Hello Hello. I seriously hope you get paid to be this freaking good. I just tested it out over the last day and I believe this is it! (I am totally doing a happy dance right now lol). Thank you again for all your help, the changes you made look wonderful and they do what I need them to. 

P.S I am glad you like the macros lol I am still a noob when it comes to that stuff but glad I was in the right ballpark. 

[#11]

@TSawyer1226  You  are very welcome and very happy it is helpful.  No I/we are just volunteers here and this isn't even directly related to my real job (I use excel but just like you or anyone else in business. i.e. not a consultant).  My payment is knowing I helped someone and seeing my Likes and Best Responses tick up :)