Tracking the cumulative days of a cell value change
I need to create column where i can track a number of days a column is checked.
in my sheet, there is a check box field for Active status and another column named Active days. when the active status is checked, then it should record the number of days it is checked in the active days column. when someone unchecks it, the active days counter goes into pause, and if someone checks again then it should resume the counter.
e.g. if the active status is checked for two days, then unchecked for a day and checked again for two days, active days should show 4 days
Best Answer
-
Sorry about that. I misunderstood the first time I read.
You will need to set up the date checked column, and you will also need to set up a second sheet as a recipient for a copy row automation that copies when the box is unchecked. This will capture the static data on the second sheet for how many days the box was checked before it was again unchecked. You will also need a unique identifier on each row (Auto-Number column).
Then the formula would be
=SUMIFS({Static Sheet Days Checked Column}, {Static Sheet Unique ID Column}, @cell = [Unique ID]@row) + IF([Checkbox Column]@row = 1, TODAY() - [Checked Date]@row, 0)
Answers
-
You would need to insert a date type column and then use a Record A Date automation to grab the date that the box is checked. From there you would use
=TODAY() - [Checked Date]@row
-
Thank you, Paul. But this will work only if the checkbox is checked or unchecked once. How will it work if for example a checkbox is checked today, then a day later it is unchecked, and next day checked again for 2 days?
Your advice will only capture days difference between latest checked date and today (giving total of 2 days), I wanted to capture and add all the days in past the box was checked (my expected outcome is total of 4 days).
-
Sorry about that. I misunderstood the first time I read.
You will need to set up the date checked column, and you will also need to set up a second sheet as a recipient for a copy row automation that copies when the box is unchecked. This will capture the static data on the second sheet for how many days the box was checked before it was again unchecked. You will also need a unique identifier on each row (Auto-Number column).
Then the formula would be
=SUMIFS({Static Sheet Days Checked Column}, {Static Sheet Unique ID Column}, @cell = [Unique ID]@row) + IF([Checkbox Column]@row = 1, TODAY() - [Checked Date]@row, 0)
-
Wah!!! This worked. although i will have to wait for next few days to validate because of the date checked field is recording today's day and everything is returning 0 but i am confident this works.
Thank you so much Paul.😀👏
-
Happy to help. 👍️
Feel free to revisit after a few days if something is off.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!