Review Process turn time Date/Time diff with multiple fields

[Jarred_Sanders]

Requesting help with a formula. Most likely an IF or Switch.

We have an escalation review process that I am trying calculate the Level 1 turn time between when the review of an escalation began (Review in Progress selected) to when it was approved or denied.

Escalation Decision (Single Select): Received, Review in Progress, Approved, Approved w/ Conditions, Additional info pending, Returned, Denied.

Escalation Decision Last Modified (Last Modified field, only updates when Escalation Decision is changed)

I was able to use the solution from a previous topic Justin_Barrett answered to grab the time stamp for when a specific Escalation Decision was an use three automations to populate a date/time field for each option. (Right now Review in Progress, Approved and Denied)

How to get the time when a field was last modified to the desired value? Formulas

This could also be done with Airtable’s native automations if your base is part of a Pro- or Enterprise-plan workspace.

I have Automation 1 update the record to “Received” when a new record is created. Automations 2, 3 & 4 update Review in Progress chosen, Approved Chosen and Denied Chosen.

End goal - Have a formula in the Level 1 turn time field that calculates the date/time difference between Review in Progress Chosen field and Approved Chosen or Denied Chosen fields, whichever one is populated (May need to add Approved w/ Conditions, Additional Info pending and Returned later on once I hear from the requestor but once I see the syntax I can probably add that on my own after if necessary)

Thank you in advance for any help!

[Rupert_Hoffsch1]

Hi @Jarred_Sanders, it seems that you’re already quite invested in your solution, but let me propose an alternative nonetheless: A separate log table!

If you would create a separate log table, you wouldn’t really need to use formulas etc. to have time stamps for all changes in your single select. You would just run an automation to log a change every time the single select is changed.

What might be a bit more complicated in this scenario is analysing the data, but the collection itself is clean.

But let me also answer your question from above :stuck_out_tongue: The formula would look like this:

IF({Approved Chosen} != "", DATETIME_DIFF({Review in Progress Chosen}, {Approved Chosen}, 'days'), DATETIME_DIFF({Review in Progress Chosen}, {Denied Chosen}, 'days'))

It’s easy for deciding between two fields (if first field is not empty → do this, if empty → do that). It gets more complicated with nested ifs when you want to add more fields to it. A Switch() might be easier then. The formula above gives you the date time difference in days and potentially you’ll have to swap the date fields within for date time diff formula depending on which values you’re getting.

Hope that helps!