Skip to main content
Solved

Date formula conflict

  • August 20, 2021
  • 2 replies
  • 34 views
P

Hey, guys!

I’m trying to add a column that return to me a specific message when attends some date criteria.
Actually, I did the formula and it was working well:

IF(AND(DAY({Final date})=DAY({Request date}),MONTH({Final date})=MONTH({Request date}),YEAR({Final date})=YEAR({Request date})),“ :warning: Warning”, IF({Final date}<{Request date},“ :red_circle: Wrong”,“ :white_check_mark: Right”))

But after some tests I noticed that something weird was happening

How you can see, the first and the second line returned strange results…
Is there something wrong with the formula?

Thanks!

Best answer by Kamille_Parks11

Is “Use same timezone for everyone” turned on (or off) for both date fields? It is possible you’re dealing with timezone issues or either of your date fields are storing a time component behind the scenes.

Try:

IF(
   DATETIME_FORMAT({Final Date}, "YYYYMMDD") = DATETIME_FORMAT({Request Date}, "YYYYMMDD"),
   "Warning",
   IF(
      DATETIME_FORMAT({Final Date}, "YYYYMMDD") < DATETIME_FORMAT({Request Date}, "YYYYMMDD"),
      "Wrong",
      "Right"
   )
)

If that doesn’t fix it, change every reference to the field names to SET_TIMEZONE({Name of field}, "your timezone"). List of timezones: Supported timezones for SET_TIMEZONE – Airtable Support

2 replies

Kamille_Parks11
Forum|alt.badge.img+27

Is “Use same timezone for everyone” turned on (or off) for both date fields? It is possible you’re dealing with timezone issues or either of your date fields are storing a time component behind the scenes.

Try:

IF(
   DATETIME_FORMAT({Final Date}, "YYYYMMDD") = DATETIME_FORMAT({Request Date}, "YYYYMMDD"),
   "Warning",
   IF(
      DATETIME_FORMAT({Final Date}, "YYYYMMDD") < DATETIME_FORMAT({Request Date}, "YYYYMMDD"),
      "Wrong",
      "Right"
   )
)

If that doesn’t fix it, change every reference to the field names to SET_TIMEZONE({Name of field}, "your timezone"). List of timezones: Supported timezones for SET_TIMEZONE – Airtable Support

P
Justin_Barrett
P
  • Pedro_Henrique
  • Author
  • New Participant
  • August 21, 2021

Is “Use same timezone for everyone” turned on (or off) for both date fields? It is possible you’re dealing with timezone issues or either of your date fields are storing a time component behind the scenes.

Try:

IF(
   DATETIME_FORMAT({Final Date}, "YYYYMMDD") = DATETIME_FORMAT({Request Date}, "YYYYMMDD"),
   "Warning",
   IF(
      DATETIME_FORMAT({Final Date}, "YYYYMMDD") < DATETIME_FORMAT({Request Date}, "YYYYMMDD"),
      "Wrong",
      "Right"
   )
)

If that doesn’t fix it, change every reference to the field names to SET_TIMEZONE({Name of field}, "your timezone"). List of timezones: Supported timezones for SET_TIMEZONE – Airtable Support


Thanks!

I was trying to solve this earlier and found the timezone option… Everything is right now.

By the way, I tried your formula and it’s worked as well. Actually, it’s much better and cleaner than mine’s! Blessed DATETIME_FORMAT.

Thank you, very much!

Terms & ConditionsAccessibility statement