Skip to main content
Solved

"Late Ad" formula

  • April 22, 2019
  • 2 replies
  • 23 views
A
Forum|alt.badge.img+2

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?

Best answer by Jeremy_Oglesby

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…).

2 replies

J
Forum|alt.badge.img+18

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…).

A
A
Forum|alt.badge.img+2
  • Andy_Taylor
  • Author
  • Participating Frequently
  • April 22, 2019

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…).


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

J
Terms & ConditionsAccessibility statement