Help

Formula to check if two dates are different

Topic Labels: Dates & Timezones
Solved
Jump to Solution
1602 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Jolomero
5 - Automation Enthusiast
5 - Automation Enthusiast

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.

CleanShot 2020-04-02 at 16.59.39@2x

Help or ideas are appreciated.

1 Solution

Accepted Solutions
Jolomero
5 - Automation Enthusiast
5 - Automation Enthusiast

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!)

See Solution in Thread

2 Replies 2

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"
) 
Jolomero
5 - Automation Enthusiast
5 - Automation Enthusiast

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!)