Tracking the cumulative days of a cell value change

asish lamichhane

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

Paul Newcome

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)

Paul Newcome

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

asish lamichhane

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

Paul Newcome

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)

asish lamichhane

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.😀👏

Paul Newcome

Happy to help. 👍️

Feel free to revisit after a few days if something is off.