Help

Date formula conflict

Topic Labels: Formulas
Solved
Jump to Solution
1264 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Pedro_Henrique
4 - Data Explorer
4 - Data Explorer

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

Airtable

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

Thanks!

1 Solution

Accepted Solutions
Kamille_Parks
16 - Uranus
16 - Uranus

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

See Solution in Thread

2 Replies 2
Kamille_Parks
16 - Uranus
16 - Uranus

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!