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?

	A​	B​	C​	D​	E​
1​	Year​	2018​	Delta​
2​	Year Begin	00:00 Mon 01 Jan 2018​	8:00​		B2: =DATE($B$1, 1, 1)
3​	BST Begin	01:00 Sun 25 Mar 2018​	9:00​		B3: =INT((DATE($B$1, 3, 31) - 1 ) / 7) * 7 + 1 + "1:00"
4​	PST Begin	02:00 Sun 29 Apr 2018​	8:00​		B4: =INT((DATE($B$1, 4, 30) - 1 ) / 7) * 7 + 1 + "2:00"
5​	PST End	02:00 Sun 04 Nov 2018​	9:00​		B5: =CEILING((DATE($B$1, 10, 31) / 7), 1) * 7 + 1 + "2:00"
6​	BST End	01:00 Sun 25 Nov 2018​	8:00​		B6: =INT((DATE($B$1, 11, 30) - 1 ) / 7) * 7 + 1 + "1:00"

 

[shg]

That needs a little help, hang on ...

 

[shg]

I think this is correct ...

	A​	B​	C​	D​	E​	F​	G​	H​
1​	Year​
2​	2018​
3​	​
4​	PT -> GT
5​	Date​	Month​	Day​	Nith​	Time​	Date/Time​	Delta​
6​	Year Begin					00:00 Mon 01 Jan 2018​	8:00​	F6: =DATE($A$2, 1, 1)
7​	BST Begin	3​	1​	0​	1:00​	17:00 Sat 24 Mar 2018​	9:00​	F7: =NthWeekDay($A$2, B7, C7, D7) + E7 - G6
8​	PST Begin	4​	1​	0​	2:00​	02:00 Sun 29 Apr 2018​	8:00​	F8: =NthWeekDay($A$2, B8, C8, D8) + E8
9​	PST End	11​	1​	1​	2:00​	02:00 Sun 04 Nov 2018​	9:00​	F9: =NthWeekDay($A$2, B9, C9, D9) + E9
10​	BST End	11​	1​	0​	1:00​	16:00 Sat 24 Nov 2018​	8:00​	F10: =NthWeekDay($A$2, B10, C10, D10) + E10 - G9
11​
12​	GT -> PT
13​	Event​	Month​	Day​	Nith​	Time​	Date/Time​	Delta​
14​	Year Begin					00:00 Mon 01 Jan 2018​	-8:00​	F14: =DATE($A$2, 1, 1)
15​	BST Begin	3​	1​	0​	1:00​	01:00 Sun 25 Mar 2018​	-9:00​	F15: =NthWeekDay($A$2, B15, C15, D15) + E15
16​	PST Begin	4​	1​	0​	2:00​	17:00 Sat 28 Apr 2018​	-8:00​	F16: =NthWeekDay($A$2, B16, C16, D16) + E16 + G15
17​	PST End	11​	1​	1​	2:00​	18:00 Sat 03 Nov 2018​	-9:00​	F17: =NthWeekDay($A$2, B17, C17, D17) + E17 + G16
18​	BST End	11​	1​	0​	1:00​	01:00 Sun 25 Nov 2018​	-8:00​	F18: =NthWeekDay($A$2, B18, C18, D18) + E18

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