Time zone conversion

[JonRowland]

Hi guys,

I've been Googling this and can't seem to find an appropriate answer.

What I am looking is for a way to bulk convert Times from Pacific Timezone (PST/PDT) into local UK time (GMT/BST). So what is needed to be known is whether the date is in Daylight Saving Time for one or both of the zones.

So for example I have
Pacific (PST) 12:00:00 01/01/2018 and need to show this converts to UK local (GMT) 20:00:00 01/01/2018.
or
Pacific (which is PDT) 13:00:00 14/03/2018 and need to show this converts to UK local (GMT) 20:00 14/03/2018

It is possible I may need to work with other time zones so curious whether there is a build in function in Excel/Windows which can help with such conversions with that knowing when Daylight Saving is in place within that zone, I can be country specific. Or has anyone had any experience of something similar using an API. Whatever way how did they work it out?

Thanks
Jon

 

[mole999]

the kicker is each zone changes at different days

 

[ranman256]

I use a table that has the time zone and the integer difference.
GMT,0,Greenwitch....
EST,-6,US Eastern Standard Time
etc

then you convert using the integer.
google for the table.

 

[JonRowland]

My dates could be from any year & like mole999 says the daylight savings change on different days and I ideally won't something I don't have to work out etc.

 

[mole999]

The best I can think of is to convert to UTC as the standard then convert to the local time, but even microsoft had to reissue the DSL file due to international changes to different counteries

 

[shg]

Lookup table?

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][td="bgcolor:#C0C0C0"]

E​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

Year​

[/td][td]

2018​

[/td][td="bgcolor:#F3F3F3"]

Delta​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]Year Begin[/td][td="bgcolor:#CCFFCC"]

00:00 Mon 01 Jan 2018​

[/td][td]

8:00​

[/td][td][/td][td="bgcolor:#CCFFCC"]B2: =DATE($B$1, 1, 1)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]BST Begin[/td][td="bgcolor:#CCFFCC"]

01:00 Sun 25 Mar 2018​

[/td][td]

9:00​

[/td][td][/td][td="bgcolor:#CCFFCC"]B3: =INT((DATE($B$1, 3, 31) - 1 ) / 7) * 7 + 1 + "1:00"[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]PST Begin[/td][td="bgcolor:#CCFFCC"]

02:00 Sun 29 Apr 2018​

[/td][td]

8:00​

[/td][td][/td][td="bgcolor:#CCFFCC"]B4: =INT((DATE($B$1, 4, 30) - 1 ) / 7) * 7 + 1 + "2:00"[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]PST End[/td][td="bgcolor:#CCFFCC"]

02:00 Sun 04 Nov 2018​

[/td][td]

9:00​

[/td][td][/td][td="bgcolor:#CCFFCC"]B5: =CEILING((DATE($B$1, 10, 31) / 7), 1) * 7 + 1 + "2:00"[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]BST End[/td][td="bgcolor:#CCFFCC"]

01:00 Sun 25 Nov 2018​

[/td][td]

8:00​

[/td][td][/td][td="bgcolor:#CCFFCC"]B6: =INT((DATE($B$1, 11, 30) - 1 ) / 7) * 7 + 1 + "1:00"[/td][/tr]
[/table]

 

[shg]

That needs a little help, hang on ...

 

[shg]

I think this is correct ...

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][td="bgcolor:#C0C0C0"]

E​

[/td][td="bgcolor:#C0C0C0"]

F​

[/td][td="bgcolor:#C0C0C0"]

G​

[/td][td="bgcolor:#C0C0C0"]

H​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

Year​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

2018​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]PT -> GT[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td="bgcolor:#F3F3F3"]

Date​

[/td][td="bgcolor:#F3F3F3"]

Month​

[/td][td="bgcolor:#F3F3F3"]

Day​

[/td][td="bgcolor:#F3F3F3"]

Nith​

[/td][td="bgcolor:#F3F3F3"]

Time​

[/td][td="bgcolor:#F3F3F3"]

Date/Time​

[/td][td="bgcolor:#F3F3F3"]

Delta​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]Year Begin[/td][td][/td][td][/td][td][/td][td][/td][td="bgcolor:#CCFFCC"]

00:00 Mon 01 Jan 2018​

[/td][td]

8:00​

[/td][td="bgcolor:#CCFFCC"]F6: =DATE($A$2, 1, 1)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]BST Begin[/td][td]

3​

[/td][td]

1​

[/td][td]

0​

[/td][td]

1:00​

[/td][td="bgcolor:#CCFFFF"]

17:00 Sat 24 Mar 2018​

[/td][td]

9:00​

[/td][td="bgcolor:#CCFFFF"]F7: =NthWeekDay($A$2, B7, C7, D7) + E7 - G6[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td]PST Begin[/td][td]

4​

[/td][td]

1​

[/td][td]

0​

[/td][td]

2:00​

[/td][td="bgcolor:#CCFFFF"]

02:00 Sun 29 Apr 2018​

[/td][td]

8:00​

[/td][td="bgcolor:#CCFFFF"]F8: =NthWeekDay($A$2, B8, C8, D8) + E8[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td]PST End[/td][td]

11​

[/td][td]

1​

[/td][td]

1​

[/td][td]

2:00​

[/td][td="bgcolor:#CCFFFF"]

02:00 Sun 04 Nov 2018​

[/td][td]

9:00​

[/td][td="bgcolor:#CCFFFF"]F9: =NthWeekDay($A$2, B9, C9, D9) + E9[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td]BST End[/td][td]

11​

[/td][td]

1​

[/td][td]

0​

[/td][td]

1:00​

[/td][td="bgcolor:#CCFFFF"]

16:00 Sat 24 Nov 2018​

[/td][td]

8:00​

[/td][td="bgcolor:#CCFFFF"]F10: =NthWeekDay($A$2, B10, C10, D10) + E10 - G9[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

11​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

12​

[/td][td]GT -> PT[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

13​

[/td][td="bgcolor:#F3F3F3"]

Event​

[/td][td="bgcolor:#F3F3F3"]

Month​

[/td][td="bgcolor:#F3F3F3"]

Day​

[/td][td="bgcolor:#F3F3F3"]

Nith​

[/td][td="bgcolor:#F3F3F3"]

Time​

[/td][td="bgcolor:#F3F3F3"]

Date/Time​

[/td][td="bgcolor:#F3F3F3"]

Delta​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

14​

[/td][td]Year Begin[/td][td][/td][td][/td][td][/td][td][/td][td="bgcolor:#CCFFCC"]

00:00 Mon 01 Jan 2018​

[/td][td]

-8:00​

[/td][td="bgcolor:#CCFFCC"]F14: =DATE($A$2, 1, 1)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

15​

[/td][td]BST Begin[/td][td]

3​

[/td][td]

1​

[/td][td]

0​

[/td][td]

1:00​

[/td][td="bgcolor:#CCFFFF"]

01:00 Sun 25 Mar 2018​

[/td][td]

-9:00​

[/td][td="bgcolor:#CCFFFF"]F15: =NthWeekDay($A$2, B15, C15, D15) + E15[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

16​

[/td][td]PST Begin[/td][td]

4​

[/td][td]

1​

[/td][td]

0​

[/td][td]

2:00​

[/td][td="bgcolor:#CCFFFF"]

17:00 Sat 28 Apr 2018​

[/td][td]

-8:00​

[/td][td="bgcolor:#CCFFFF"]F16: =NthWeekDay($A$2, B16, C16, D16) + E16 + G15[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

17​

[/td][td]PST End[/td][td]

11​

[/td][td]

1​

[/td][td]

1​

[/td][td]

2:00​

[/td][td="bgcolor:#CCFFFF"]

18:00 Sat 03 Nov 2018​

[/td][td]

-9:00​

[/td][td="bgcolor:#CCFFFF"]F17: =NthWeekDay($A$2, B17, C17, D17) + E17 + G16[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

18​

[/td][td]BST End[/td][td]

11​

[/td][td]

1​

[/td][td]

0​

[/td][td]

1:00​

[/td][td="bgcolor:#CCFFFF"]

01:00 Sun 25 Nov 2018​

[/td][td]

-8:00​

[/td][td="bgcolor:#CCFFFF"]F18: =NthWeekDay($A$2, B18, C18, D18) + E18[/td][/tr]
[/table]

The UDF:

Public Function NthWeekDay(iYr As Integer, iMo As Integer, _
                           iWkDay As Integer, nth As Integer) As Date
  ' shg 2008, 2018 (added 1904 date fix)
  ' UDF Only
  
  ' Returns the date of the nth iWkDay (1 = Sunday) in iMo in iYr
  ' If nth = 0, returns the last iWkDay in iMo

  Dim iDay          As Integer

  If nth > 5 Then nth = 5
  If nth <= 0 Then nth = 5
  iDay = 1 + 7 * nth - Weekday(DateSerial(iYr, iMo, 1), (iWkDay + 1) Mod 8)

  NthWeekDay = DateSerial(iYr, iMo, iDay)
  If Month(NthWeekDay) <> iMo Then NthWeekDay = NthWeekDay - 7
  If Application.Caller.Worksheet.Parent.Date1904 Then NthWeekDay = NthWeekDay - 1462
End Function