"Late Ad" formula

[Andy_Taylor]

I have people submitting records through a form. Records have {Date Ordered} field with creation date. The records are ads that go into publications so there’s a {Print Date} field. I want to flag all orders that come in the day before or the day of print date with “Late Ad” how do I do this?

[Jeremy_Oglesby · Accepted solution]

Hey @Andy_Taylor,

This should be pretty simple, if I understand it correctly. There’s a DATEFIME_DIFF() function that will get you what you want:

IF(
   DATETIME_DIFF(
      {Date Ordered},
      {Print Date},
      'days'
   ) > -2,
   "⛔️ Late Ad"
)

Since {Print Date} will be subtracted from {Date Ordered}, any ads submitted that are not “Late” will return a value of -2 days or lower (where -2 means submitted 2 days before {Print Date}, -3 means submitted 3 days before {Print Date}, and so on). So anything greater than -2 is an ad that was submitted the day before, day of, or else after (which is impossible, but the case is covered still…).

[Andy_Taylor]

Thank you! I couldn’t wrap my brain around it. Very helpful!