Formula statement with time and time zone
My database is vendor contacts who may be in different time zones and work different hours. I'm trying to find a way to make it obvious to the person looking at the record to know if if the contact i...
Forum Discussion
Yes, all of our users are in the Pacific time zone.
------------------------------
Kim G
------------------------------
MarkShnier__You
Qrew Legend
Qrew LegendSorry for bing slow to respond. I'm half on vacation this week.
Not tested for Syntax but try this approach
var duration TimeZoneAdjustment = Case ([TimeZone],
"Pacific", Hours(0),
"Mountain, Hours (1),
"Central", Hours(2),
"Eastern", Hours(3))
var TimeOfDay AdjustedTime = ToTimeOfDay(Now() + $TimeZoneAdjustment);
IF($AdjustedTime >= [Time Open] and $AdjustedTime <= [Time Closed), "Open", "Closed")
var text
------------------------------
Mark Shnier (Your Quickbase Coach)
mark.shnier@gmail.com
------------------------------
Thanks Mark! That works great!
Since I realized we don't always have the opening hours listed I added a second statement to capture the actual close time.
Then I adjusted so it actually shows the local time too so on those accounts without an open/close time listed (particularly overseas vendors) we can have an idea if it might be the right time of day to call.
Instead of "Open" it's now "Open. The local time is " &$AdjustedTime
Thanks for taking time out of your vacation to help me out. My fellow QB users and I really appreciate it.
Kim
------------------------------
Kim G
------------------------------Mark, How would I write formula to adjust Arizona time? They do not observe Daylight Saving time. So Arizona is on Pacific time zone (also called 'Mountain Standard' while rest of US observes Daylight Saving time). Then switches to Mountain on first Sunday in November (when Daylight Saving time ends).
Thank you,
------------------------------
Lorre Levy
------------------------------You would need to mark an exception for Arizona to move it between PST/MST depending on the current date/daylight savings Period.
You could do something like:
var text adjustedTimeZone = if(
[State]="Arizona", if( //logic for first Sunday of Month, "Mountain","Pacific"),
[TimeZone]);
var duration TimeZoneAdjustment = Case ($adjustedTimeZone,
"Pacific", Hours(0),
"Mountain, Hours (1),
"Central", Hours(2),
"Eastern", Hours(3))
------------------------------
Chayce Duncan
------------------------------Thank you, Chayce. I will play around with that. Appreciate the fast response. Have a good weekend.
------------------------------
Lorre
------------------------------
