Calculating Date Range Based on Status Change

Amber Jackson

Hi there gurus!

I need help creating a formula to calculate a date range. I know I need to subtract two dates; that's the easy part. The difficulty I'm facing is automating my second date. Is there a way to have Smartsheet automatically enter the date that a status changes to a particular value in the dropdown menu? I need to know the date that a status box was changed from "in testing" to "in review". That will give me the second date I need for my calculated range.

Any ideas?

Andrée Starå

Hi @Amber Jackson

You can use the new Record a date feature.

More info.

https://help.smartsheet.com/articles/2481986-set-the-current-date-with-record-a-date-action

Would that work/help?

I hope that helps!

Have a fantastic day & Happy New Year!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Rachelle McKeown

Hello Amber,

Off the top of my head, I know there is a Modified (Date) system column type that will create a time stamp when a row is modified. Have you tried this?

https://help.smartsheet.com/articles/1964567-system-columns

You could then lock rocks when they are changed, to avoid this date being updated again in the future. If you need to repeatedly keep track of status changes for one row item, this may get tricky. You might have to play around with moving rows to different sheets, or copying modified dates to different columns based on what the status box value is.

Rachelle

Andrée Starå

Hi @Amber Jackson

You can use the new Record a date feature.

More info.

https://help.smartsheet.com/articles/2481986-set-the-current-date-with-record-a-date-action

Would that work/help?

I hope that helps!

Have a fantastic day & Happy New Year!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Amber Jackson

@Andrée Starå that worked beautifully! Thank you so much for the help!!

Andrée Starå

@Amber Jackson

Excellent!

You're more than welcome!