Formula to Capture Total Days Open with Status Changes

HarryS

I hope that everyone is well! :)

I have a use case where I need a formula that allows me to capture the number of days a request has been open but stopping and starting the clock when the status changes. From: In Progress to On Hold to In Progress to Closed. Also, I would only like to count weekdays. (No weekends or holidays).

The columns that I created to capture the dates are:

Review Start Date (Automation - When rows are changed, and status changes to In Progress, record a date in this column)

Review On Hold Date (Automation - When rows are changed, and status changes to On Hold, record a date in this column)

Review Resumed Date (Automation - When rows are changed, and status changes to In Progress, record a date in this column)

Closed Date (Automation - When rows are changed, and status changes to Closed, record a date in this column)

Any help would be appreciated!

Matt Lynn ACT

@HarryS You can use a record date automation when it changes and create a hidden helper date column for each of the status options, then auto record when it changes to that status in it's helper column. Then do another helper column and use a =networkday() or =networkdays() function between the two dates of choice to measure the weekdays only. However you wouldn't be able to track it if it was to go back and forth between the same statuses multiple times. It doesn't have a running clock that will start and stop. The record date automation will override the previous to the latest time it has changed to that status.