Tracking the cumulative days of a cell value change

Options
asish lamichhane
asish lamichhane ✭✭✭
edited 01/13/23 in Formulas and Functions

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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    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
    Options

    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
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    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
    Options

    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
    Paul Newcome ✭✭✭✭✭✭
    Options

    Happy to help. 👍️


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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!