Counting days using formula column

Our team tracks the recruiting process using One of the metrics we want to track is how long an applicant is in progress.

I am using the formula column currently to count the number of days between today and the application date. This is working as expected, but the issue we are running into is once a candidate reaches a done phase, the counting continues so we do not have accurate historical data on candidate time in progress.

The current work around we are using is creating another date column that needs to be filled out when the recruiting process is completed, but the issue with this workaround is we get an error in the cell until the end date is entered. This is an issue because we would like to see the days in progress for active applicants to bring to our attention if an applicant has been waiting longer than anticipated.

Is it possible to stop counting based on a trigger in the formula column? For example, if status is Offer Extended, stop counting?

Hi @kaylak - You could so something like this (I am not clear on the names of your columns but used {Application Date} and {Start Date}). The first IF() statement checks to ensure the Application Date is filled in otherwise it will show an error.

IF({Application Date}<>"",IF({Status}="Done",ROUND(DAYS({Start Date},{Application Date}),0),ROUND(DAYS(TODAY(),{Application Date}),0)),"")

Hope this helps!

Hi @mark.anley,

Thanks so much for your response, this is exactly what I am looking to accomplish.
I do have one follow up question for you regarding the statuses.
Our statuses are:
-Application Review
-Interviews In Progress
-Offer Pending
-Offer Extended
-Offer Accepted
-Offer Declined/Unqualified

The bottom 3 statuses are considered our “Done” statuses - is it possible to identify multiple statuses in the “If” statement?

@kaylak - no problem! This should do it:

IF({Application Date}<>"",IF(OR({Status}="Offer Accepted",{Status}="Offer Extended",{Status}="Offer Declined/Unqualified"),ROUND(DAYS({Start Date},{Application Date}),0),ROUND(DAYS(TODAY(),{Application Date}),0)),"")