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 is reachable at a given moment. 

We are on Pacific Time. If I have a vendor on Eastern Time who is open from 8:00 AM - 4:00 PM EST, how can I make it obvious to my QB user looking at the record at 3:00 PM PST, that this vendor is closed.

I created these fields:

  • TimeZone - multiselect
  • Time Open - Time
  • Time Closed - Time
  • Time Warning - Formula Rich Text

I'm trying to write something like this:

If ([TimeZone]="Pacific" AND NOW>[Time Open] AND NOW<[Time Closed]), then "Open"), 

If ([TimeZone]="Eastern" AND (NOW-3)>[Time Open] and Now-3<[Time Closed], then Open), else "They're closed right now."

I know this formula syntax is wrong.

Does anyone know what it would take to make it work?

Or alternatively if there are better suggestions, I'm not locked into this setup. We do have vendors overseas so a different type of message that includes some clue to when would be the right time to call would also be helpful.

I just don't want them to have to think about what time it is somewhere else.

Thanks,

Kim



------------------------------
Kim G
------------------------------

8 Replies

Sort By
  • MarkShnier__You's avatar
    MarkShnier__You
    Qrew Champion
    11 months ago

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