Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Populate "Urgent" when {Due Date} is within 7 days of TODAY and {Date Filed} is EMPTY

Topic Labels: Dates & Timezones Formulas
1109 5
cancel
Showing results for 
Search instead for 
Did you mean: 
Rlbake01
4 - Data Explorer
4 - Data Explorer

Hi, 

I am trying to populate in a cell, "Urgent" when a project is coming up on its {Due Date} within 7 days of today AND the cell that has my completed date, {Date Filed} is Empty. If {Date Filed} is Not Empty, i'd like it to populate "Completed". If {Due Date} is over 7 days away and {Date Filed} is Empty, i'd like it to populate "Pending".

Any assistance is appreciated. Thanks!

 

5 Replies 5

Hi,
Use formula with DATETIME_DIFF
I'd start with
IF( {Date Filed} , 'Completed,
  IF(7>=ABS(DATETIME_DIFF... ,
    IF(IS_AFTER....

Rlbake01
4 - Data Explorer
4 - Data Explorer

Hi,

Sorry for my ignorance. Could you show how that might look more drawn out? I was easily able to get the first formula indicating "Completed", but the 2 following I'm having issues writing out and not getting an error. 

Thanks!

Hi,
https://support.airtable.com/docs/formula-field-reference#date-and-time-functions

Alexey_Gusev_0-1701790422475.pngAlexey_Gusev_1-1701790455263.pngAlexey_Gusev_2-1701790526673.png


Well, the about of brackets is quite hard to set them properly, without experience . I would recommend to use wider editor window, or notepad to find/replace, and code-like style to not mess with statements

that should work

IF({Date Filed},'Completed',
  IF(7>=ABS(DATETIME_DIFF(TODAY(),{Due Date},'days')),
    IF(IS_AFTER({Due Date},TODAY()),'Urgent','Pending')
   )
)
Rlbake01
4 - Data Explorer
4 - Data Explorer

That is super helpful Alexey. That worked and I appreciate the feedback as well while I get started with AirTable. 

I didnt think of it till just now, but would you be able to provide an edit to include something that will change the cell content to show, "Past Due", if the due date is past due and hasnt been filed? Currently it shows "Pending" when past due. 

Hi,
if I understood you well, it's quite simple - just substitute text in formula, set "Past Due" instead of "Pending".

 

 

IF({Date Filed},'Completed',
  IF(7>=ABS(DATETIME_DIFF(TODAY(),{Due Date},'days')),
    IF(IS_AFTER({Due Date},TODAY()),'Urgent','Past Due')
   )
)