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.
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 Perfect! it has been a short adventure lol but thank you again for all of your help.
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.