Excel Question: I need a date and time formula to convert between time zones

Question

Hello,

I am trying to find a way to calculate a duration in days between my, time zone (Central), and (Pacific; Mountain; Eastern). Just do not know where to start. My criteria would be as follows:

Cell C5:C100 would be the timestamps in this format:3/18/2010 23:45 but for different dates and times. Cell D5:D100 would be the corresponding timezone in text form: Pacific; Mountain; Eastern; Central.

Cell F5 would be where the duration in days would need to be.

Just not sure how to write the formula to give me what I am looking for. I appreciate any assistance in advance. Thanks

Answer 1

From Time Zones And Daylight Savings Time

Neither VBA nor VB6 provides any native functions for working with Time Zones, Greenwich Mean Time (GMT, also called UTC), or Daylight Savings Time. To work with these values, you must use some Windows Application Programmatic Interface (API) functions. This page describes these API functions and how to use them in your VBA code.

Answer 2

The simplest way is probably a nested IF formula:

=IF(D22="Pacific",(1/24*2),IF(D22="Mountain",(1/24),IF(D22="Eastern",-(1/24),0)))

Or using the LOOKUP function to return the same:

=(C5+(1/24*LOOKUP(D5,{"Central","Eastern","Mountain","Pacific"},{-2,-3,-1,0})))-C5

Alternatively, you could create a UDF that takes the local date/time and works out the Pacific date/time from the textual representation of the timezone:

Function TimeDifference(LocalTime As Range, TimeZone As Range) As Date
    Application.Volatile
    Dim TimeOffset As Long

    Select Case TimeZone
        Case "Mountain"
            TimeOffset = -1
        Case "Central"
            TimeOffset = -2
        Case "Eastern"
            TimeOffset = -3
        Case Else
            TimeOffset = 0
    End Select
    TimeDifference = (1 / 24 * TimeOffset)
End Function

You would call this from column F like this:

=TIMEDIFFERENCE(C5,D5)