Formula question

Lucinda Bugbee

I am writing a formula to figure the duration of a status. I have a Status dropdown column, Date Column (captures date the status changes) and a Duration column that calculates "today - date column" for the duration.

Now though, when I change the status, it triggers the next status to start the cycle, but it keeps calculating for the first status. How do I "Stop" the count of days of the first status?

I added helper column with a checkmark to view against the status, but if I compare against the checkmark the entire duration goes away.

Thanks!

Mark.poole

@Lucinda Bugbee

To be sure I am understanding. Each status has its own date column that marks the date the status started?

Lucinda Bugbee

Yes, that is correct. Also, the statuses are not sequential. They can skip steps at times, so it is not as easily as subtracting one column from the next.

Mark.poole

@Lucinda Bugbee

Try changing the check flag to a date column as well. Then Use automation to record a date in that spot when it starts a new process. Set the automation to only record the date if the cell is blank. Then you can do your formula for a duration. Subtracting the start date of each step from its own end date.

Lucinda Bugbee

Thanks Mark for the response. This works for me if all the statues are sequential. What I have done is created automations for "Record a date" for the start date and end date. Once these are filled, the duration is calculated by subtracting the start date from the end date.

However, if my process goes back to a previous status for example COMM Revised Submittal Posted (Blue columns) to Returned for Review and Comment (Green columns) only part of the process works. It will record the new start date, but not the end date again.

What I need to have happen is if the process goes back a step, I need to clear out the end date so I can re-calculate the duration.

So… in this example: The project steps went:

In Progress - 2/5/25
Returned for Review / Comment - 2/10/25
COMM Revised Submittal Posted - 2/15/25
Went back to Returned for Review / Comment - 2/18/25

I am not sure how to get around this problem. Thanks!