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

[Rlbake01]

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!

[Alexey_Gusev]

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

[Rlbake01]

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!

[Alexey_Gusev]

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

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]

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. 

[Alexey_Gusev]

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')
   )
)