Forum Discussion

KimG's avatar
KimG
Qrew Cadet
11 months ago

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...
MarkShnier__You's avatar
MarkShnier__You
Qrew Champion

Are all of your used in the Pacific Time Zome?



------------------------------
Mark Shnier (Your Quickbase Coach)
mark.shnier@gmail.com
------------------------------
KimG's avatar
KimG
Qrew Cadet
11 months ago

Yes, all of our users are in the Pacific time zone.



------------------------------
Kim G
------------------------------
  • MarkShnier__You's avatar
    MarkShnier__You
    Qrew Champion
    11 months ago

    Sorry 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
    ------------------------------
    • KimG's avatar
      KimG
      Qrew Cadet
      11 months ago

      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
      ------------------------------
    • LorreLevy's avatar
      LorreLevy
      Qrew Member
      6 months ago

      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
      ------------------------------
      • ChayceDuncan's avatar
        ChayceDuncan
        Qrew Captain
        6 months ago

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