Skip to main content
Solved

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

  • July 27, 2023
  • 2 replies
  • 21 views

Forum|alt.badge.img+3

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

Best answer by AirOps

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

 

 

2 replies

AirOps
Forum|alt.badge.img+10
  • Participating Frequently
  • Answer
  • August 1, 2023

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

 

 


Forum|alt.badge.img+3
  • Author
  • New Participant
  • August 1, 2023

Thanks Chantal