Is a formula to calculate Date and Time across timezones for a single event possible?
I have board tracking events. It has a date column, with the time field visible.
I also have two global time clock fields. One called ‘local timezone’ the second ‘Attendee Timezone’
Then I have a simple formula field that currently tells the difference between the two timezones. (If local timezone = Manila and Attendee Timezone = Sydney, it would return -03:00).
What I really want is a Formula column that outputs the Date and time of the event for the Attendee based on their timezone.
Managed to get the outcome I was looking for after reading a whole bunch of different threads with formulas! thanks community!
Might not be the most efficient, but this is what i went with: FORMAT_DATE(ADD_MINUTES({Date},(HOURS_DIFF({Local Timezone}, {Sydney TZ})*-60)), “MMM DD, HH:mm, A”)
If anyone knows how to add conditional formatting to that, please let me know! basically between 8AM and 8PM “daytime/evening” one colour and outside of that another.
You can use conditional coloring as pictured below. In your case, you would need filters for each hour (Yellow when formula contains 9am/9:00, Blue when formula contains 9pm/21:00 etc. ).
