Before describing how the conversion is accomplished here are two examples. The first uses spreadsheet formulas only. The advantage of this method is that no VBA code is required. The disadvantage is that it is cumbersome to add the conversion formulas in multiple cells. The second method -- and the preferred method -- uses a VBA add-in to achieve its goals.
The example below converts the time in cell $D$4, which as cell $C$4 indicates is Eastern Daylight Time (EDT). The corresponding time in Sydney (cell $C$6) is shown in cell $D$6.
Cells $D$5 and $E$5 contain intermediate formulas (described in xx).
This method is much easier to use. The add-in includes the three functions described in the example below. The first, TZToGMT converts any time to GMT (also known as UTC). The second function, TZFromGMT converts any time from GMT to the local time in a specified time zone. The third function TZNewTimeZone converts the local time in one zone to the local time in another zone.
Cell $D$17 contains the function =TZToGMT (D4,C4,TimeZoneDB). It converts the time in $D$4 (which, as specified in cell $C$4, happens to be EDT time) to GMT.
Similarly, the function =TZFromGMT (D12,C6,TimeZoneDB) in cell $D$20 contains the result of converting GMT time to the local time in Syndey, Australia.
Finally, consider the function =TZNewTimeZone (D4,C4,C6, TimeZoneDB) in cell $D$23. It converts the time in cell $D$4, which happens to be EDT time (specified in cell $C$4) to the time in Syndey, Australia (specified in cell $C$6).
Option 1 (recommended): Download the file TimeZoneConvert.zip. After unzipping the file, there will be two files on your disk -- TimeZoneConvert.xla, and TimeZoneExamples.xls. Install the TimeZoneConvert.xla as an add-in. This will add three user-defined functions that you can use. The functions are TZToGMT, TZFromGMT, and TZNewTimeZone (see the examples section on how to use them).
The TimeZoneExamples.xls, in addition to containing examples also contains the database for conversion. The file must be open for you to use the conversion functions! [In the next version of the functions, the database will be part of the add-in itself.]
Option 2: Download the file TimeZoneConvert.zip. After unzipping the file, there will be two files on your disk -- TimeZoneConvert.xla, and TimeZoneExamples.xls. Open the TimeZoneExamples.xls file and copy the spreadsheet formulas as you need them.
Again, the TimeZoneExamples.xls, in addition to containing examples also contains the database for conversion. The file must be open for you to use the conversion functions! [In the next version of the functions, the database will be part of the add-in itself.]
Option 3: You can copy the code from the Web pages and follow the directions as described in the sections, "The TimeZone VBA functions" or "The TimeZone spreadsheet functions"
Note that both options 2 and 3 will require that you adjust the formulas to your spreadsheet.
The database used for converting from one time zone to another is actually quite straightforward. It is in the file TimeZoneExamples.xls (which is why that file must be open before any of the functions in TimeZoneConvert.xla can be used). For each time zone the database contains three pieces of information.
In the first empty cell in column B enter how you want to identify the new time zone. In the next cell (in column C) enter the local. time (using a 24 hour clock) corresponding to midday GMT. Finally, copy the formula from the cell in column D of the the prior row to the cell in column D of the current row. You are now ready to use the new time zone in your calculations.
Times between midnight and midday are represented in the 'normal' fashion, i.e., the time goes from 00:00 (midnight) through 03:00 (3 a.m.) to 08:00 (8 a.m.) to 12:00 (midday).
To convert time from a 12 hour clock to a 24 hour clock, add 12 to the time in p.m. So, the time goes from 12:00 (midday) to 13:00 (1 p.m.) to 18:00 (6 p.m.) to 21:00 (9 p.m.) to 23:00 (11 p.m.) to 00:00 (midnight).
TZToGMT converts from local time to GMT by calling the following function:
Function ToGMT(aTime As
Date, fromCode As String, TimeZoneDB As Range)
'aTime contains a Date/Time that
needs to the converted to GMT _
fromCode is a time zone id
(such as EDT) associated with aTime _
and TimeZoneDB is a 2-D
range with the time difference between GMT _
and various time
zones. The layout is: _
col.
1
col.
2
col. 3 _
GMT
12:00 (midday)
local time - GMT _
<local
id> time corr.
to local
time - GMT _
midday GMT
Dim rslt, temp1
On Error GoTo BadTimeZoneCode
rslt =
-Application.WorksheetFunction.VLookup(fromCode,
TimeZoneDB, 3, False)
On Error GoTo 0
temp1 = TimeValue(Fix(Abs(rslt))
& ":" & (Abs(rslt) -
Fix(Abs(rslt))) * 60 & ":0")
ToGMT = aTime + Sgn(rslt) * temp1
Exit Function
BadTimeZoneCode: ToGMT = "Unknown time
zone:" & fromCode
End Function
'ToGMT
TZFromGMT converts from GMT to a local time by calling the following function:
Function FromGMT(aTime
As Date, toCode As String, TimeZoneDB As Range)
'see toGMT for an explanation of
parameters
Dim rslt, temp1
On Error GoTo BadTimeZoneCode
rslt =
Application.WorksheetFunction.VLookup(toCode,
TimeZoneDB, 3, False)
On Error GoTo 0
temp1 = TimeValue(Fix(Abs(rslt))
& ":" & (Abs(rslt) -
Fix(Abs(rslt))) * 60 & ":0")
FromGMT = aTime + Sgn(rslt) *
temp1
Exit Function
BadTimeZoneCode: FromGMT = "Unknown time
zone:" & toCode
End Function
'FromGMT
TZNewTimeZone converts from one local time to another by first calling the TZToGMT function above and then using that result to call the TZFromGMT function.
The spreadsheet fomula for converting the time from one time zone to another works very similarly to how the VBA function TZNewTimeZone works. However, given the various checks and corrections needed, the calculation is split into three cells (where the first two contain intermediate results).
An example calculation is shown below:
The first intermediate formula (in cell $D$5) calculates the time difference (in hours) between the timezones (given in $C$4 -- EDT -- and $C$6 -- Sydney). The formula is:
=-VLOOKUP(C4,TimeZoneDB,3,FALSE) +VLOOKUP(C6,TimeZoneDB,3,FALSE)
The second intermediate formula (in cell $E$5) converts the time difference into a form that Excel interprets as a time value
=TIME(TRUNC(ABS(D5)),(ABS(D5) -TRUNC(ABS(D5)))*60,0)
The final result is assembled in cell $D$6:
=IF(ISERROR(D5),"Unknown timezone", IF(D5<0,IF(D4<E5,D4-E5+2*TIME(12,0,0),D4-E5),D4+E5))
The immediate plan is to integrate the database (which is currently in TimeZoneExamples.xls) into the TimeZoneConvert.xla add-in. At that time, a new macro, to facilitate maintenance of the database, will be included.
