Skip to main content

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”, IF({Final date}<{Request date},“ 🔴 Wrong”,“ ✅ 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!

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

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!

Reply


Terms & Conditions