Skip to main content

I am trying to determine if an appointment date (stored as UTC) is on the same day or a different day depending on the timezone.


I think I know how to do this with an IF statement in general:

IF(“Date in CTZ1”=“Date in CTZ2",“same”,“different”)

but I don’t know if they work with dates and times.


In the image Call 1 is a date in UTC. I want to compare if that date is the same in Sydney (CTZ1) as it is in Nicosia (CTZ2) - which it isn’t - Sydney is on the next day.

I need another field that says ‘same’ or ‘not same’, but I can’t figure out how to do it.



Help or ideas are appreciated.

To see the date/time for a particular timezone, use the SET_TIMEZONE function with the DATETIME_FORMAT function. Both are documented in the formula field reference.


IF(

  DATETIME_FORMAT(SET_TIMEZONE({Call 1}, {CTZ1}), 'DD') = 

  DATETIME_FORMAT(SET_TIMEZONE({Call 1}, {CTZ2}), 'DD'),

  "same",

  "not same"

)

To see the date/time for a particular timezone, use the SET_TIMEZONE function with the DATETIME_FORMAT function. Both are documented in the formula field reference.


IF(

  DATETIME_FORMAT(SET_TIMEZONE({Call 1}, {CTZ1}), 'DD') = 

  DATETIME_FORMAT(SET_TIMEZONE({Call 1}, {CTZ2}), 'DD'),

  "same",

  "not same"

)

Thank you that worked fine, and I appreciate that direction for learning more, as well. None of this is anywhere in my area of expertise!


I’m off to figure out how to then show which one of the two is the later date. (Not strictly necessary for what I’m doing, but it’s where my mind went!)

Reply


Terms & Conditions