Forum Discussion

ldbrassard's avatar
ldbrassard
Qrew Member
8 days ago

Time Zone change

Good morning,

I am building a Transportation application for my team to use. When we request a pickup time from our transportation vendor, it is always in central time since we are based in Texas. Can QB convert the central time to local time based on my selected city?

For example, I have an employee who needs to be picked up at LAX at 10 a.m. Pacific time, but we can only enter 12 p.m. Central time. How can I build a formula in QB that will convert noon central to 10 AM when I select LAX as a city? The same goes for all the cities we service. How can I make sure Daylight savings are accounted for?

I have attached a spreadsheet of the cities we service regarding their summer and winter times.

formulas
  • DonLarson's avatar
    DonLarson
    Qrew Elite
    3 days ago

    I would build your Transportation table differently

    UTC Offset would be a Numeric Field

    Local Time Pick Up would be a Date/Time field

    [Pick Up]+Hours(7)+Hours(UTC Offset)

    This takes the MST Time then converts it to UTC and then to the time zone of the local airport.   

    This does not solve for DST.

     

  • ldbrassard31's avatar
    ldbrassard31
    Qrew Member
    3 days ago

    Thank you! 
    I am still so new at QB, and this all helps, but I still need some help. 

    I created a table called "Transportation Vendors" with the Airport code, Vendor Name, Phone number, email, time zone, and UTC offset listed. On my request form, I have the Airport code Central time pickup time and the "Local time Pickup time."  
    On my request form, if I chose LAX airport and entered a pickup time of 12 p.m. Central, how would the Local pickup time be calculated to show 10 a.m.?  I have the local time pickup time field set as a “Time” Formula.

     

     

  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    4 days ago

    The way to approach this is to have a table where the Key field is the city / airport code.  On that table you would have an offset  to central time.   The make a relationship back to the Pickups table.  That offset with be looked up into the Pickups table and used to calculate the local time.

    • DonLarson's avatar
      DonLarson
      Qrew Elite
      3 days ago

      Expanding on Mark's answer, you should have two offsets.  One for Standard Time and one for DST.    There are places that do not change the clocks, particularly Arizona does not observe DST.  Same with parts of Indiana and so on.

      Then you make an If Statement in the Pick Ups table to use either offset on whether or not Today() is Standard or DST.