Formula to show in progress, under time and overdue depending on other fields

[CameronG]

Hi guys,

I currently have the below in a grid view.

I would like to have a formula in the "Timeframe Status" column which will do the following:

1. Check whether text has been added to the "RFI Resolution" field.

2. If the "RFI Resolution" field is not empty, then check the hours between the "Date Created" field and the "Due by" field. If this is less than 48 hours, then return the text "Within time". If it is equal to or greater than 48 hours, then return the text "Past due". I would like the hours to be shown decimal 0.00

3. If the "RFI Resolution" field is empty, then return the text "Ongoing". Again, I would like the hours to be shown decimal 0.00

Thanks

[AirOps · Accepted solution]

Hi @CameronG, 

Try this: 

IF({RFI Resolution},

IF((DATETIME_DIFF({Due by}, {Date Created}, 'minutes')/60) < 48,

"Within Time: " & ROUND((DATETIME_DIFF({Due by}, {Date Created}, 'minutes')/60), 1.00),

"Past Due: " & ROUND((DATETIME_DIFF({Due by}, {Date Created}, 'minutes')/60), 1.00)),

"Ongoing: " & ROUND((DATETIME_DIFF({Due by}, {Date Created}, 'minutes')/60), 1.00)

)

I hope this helps! 

Chantal

[CameronG]

Thanks Chantal