Date formula conflict

[Pedro_Henrique]

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!

[Kamille_Parks · Accepted solution]

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

[Pedro_Henrique]

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!