DATETIME_DIFF within IF/AND/OR formula

[Laura_Connolly]

I am trying to create a field that will show a flag when one of two conditions are met around a Platform Due Date:

• Delivery Date is after the Platform Due Date
• Submitted Date is within 56 days (or 8 weeks) of Platform Due Date

I also don’t want it to show anything if there’s no Platform Due Date (field is blank), as this field/column doesn’t apply for all records.

I’m not getting any errors, but it’s also not showing the flag for a record that was submitted 3/29/22 but was due 3/7/22. In this case, the submitted date is after the due date, but there will be future cases where the submitted date is before the due date, but still less than 8 weeks or 56 days before.

Here’s what I’ve got:

IF(AND({Delivery Date} > {Platform Due Date}, OR(DATETIME_DIFF({Submitted Date}, {Platform Due Date}, ‘days’ < 56)), {Platform Due Date}, “Field Is Not Empty”), “ :x: PAST DEADLINE”, “”)

[Kamille_Parks]

Since your original formula is only testing two conditions, logically, you can’t use both AND and OR. Either both conditions must be true (AND) or only one condition must be true (OR). Also your parenthesis are in the wrong spots in a lot of places.

If you want to add in the third conditional “{Platform Due Date} must have a value”, then you can use AND and OR.

IF(
   AND(
      {Platform Due Date},
      OR(
         {Delivery Date} > {Platform Due Date},
         DATETIME_DIFF({Submitted Date}, {Platform Due Date}, 'days') < 56
      )
   ), 
   ":x:PAST DEADLINE"
)

[Laura_Connolly]

Thanks for explaining. I’m new to Airtable and obviously do not quite fully grasp the formulas and their structure yet.

I tried plugging this in, and while it’s now giving me the PAST DEADLINE flag, it is only correctly flagging the delivery date > platform due date portion. The DATETIME_DIFF portion is flagging anything with a submitted date, regardless of whether it is less than 56 days of the due date. For example, I put in a submitted date in March for a due date in November and it still showed the PAST DEADLINE flag. Any ideas? :frowning:

[Kamille_Parks · Accepted solution]

Your dates are the wrong way round. If 3/1/2022 is the Submitted Date and 11/1/2022 is the Due Date, and the formula is:

DATETIME_DIFF({Submitted Date}, {Platform Due Date}, 'days')

Then the result of that would be “-251”. -251 is less than 56. So reverse the position of the two date fields in the formula. For DATETIME_DIFF, the later date should be the first argument unless you want a negative output.

[Laura_Connolly]

Thanks so much… that worked. I did get to thinking about negative values and how that would affect it but I started to confuse myself. Looks good now!