Convert date AND time FROM UTC into PST AND MST with daylight savings

[charliechaz]

I've been searching through multiple threads and posts regarding the date/time conversion in excel. I did find a formula by Allen Wyatt that works (for the most part) but fails to consider time. For example, I know that on March 8 2020 at 2:00 AM, both MST and PST go to daylight savings time and on November the 1st 2020 at 2:00 AM, daylight savings end. So my formulas:
(A2 is the UTC timestamp)
for MST: =IF(AND(A2>=DATEVALUE("3/8/2020 02:00"),A2<=DATEVALUE("11/01/2020 02:00")),A2-6/24,A2-7/24)
for PST: =IF(AND(A2>=DATEVALUE("3/8/2020 02:00"),A2<=DATEVALUE("11/01/2020 02:00")),A2-7/24,A2-8/24)

The formula recognizes the date, but when I present '3/8/2020 1:27:14 AM', a time when DST does not yet apply, it fails to calculate the timestamp (date and time) correctly because the time portion is not considered.

I was thinking to extract the date using the INT function and time using the HOUR function. But it all got complicated when adding ANDs.

Any help is appreciated!

[MrShorty]

I see two issues:

1) You are correct, DATEVALUE("3/8/2020 02:00") returns only the date portion of the text string. You need something that will return both date and time portion. Maybe:
DATE(2020,3,8)+TIME(2,0,0) or
DATEVALUE("3/8/2020")+2/24 or similar

2) You say that daylight savings time begins on March 8 2020 at 2 AM and ends on Nov 1 2020 at 2 AM. Which is true for LOCAL time, but you state that your time stamp in A2 is a universal time. In order to correctly handle the transition, you will need to know the UTC time for the daylight savings time changes for each time zone.

With those two changes into the existing formula for the mountain time zone:

Please Login or Register  to view this content.

(UTC to local and back always confuses me, so if I got 6's and 7's in the wrong place, fix those up as needed).

Note that I prefer the DATE() and TIME() functions over the DATEVALUE() and TIMEVALUE() functions here -- simply because I don't want to rely on how Excel interacts with my OS's regional settings in interpreting date and time text strings.

[charliechaz]

Thank you MrShorty. I didn't understand your formula entirely, especially the portion +TIME(7,0,0). I assume that this is the UTC - X hours (depending on MST pr PST).
I didn't get it cause as far as the UTC difference, I thought this is provided in the end as ,A2-6/24,A2-7/24).
So in the end I deleted the "+TIME(7,0,0)" portion of the formula as:

For MST: =IF(AND(A2>=DATE(2020,3,8)+TIME(2,0,0),A2<=DATE(2020,11,1)+TIME(2,0,0)),A2-6/24,A2-7/24)
For PST: =IF(AND(A2>=DATE(2020,3,8)+TIME(2,0,0),A2<=DATE(2020,11,1)+TIME(2,0,0)),A2-7/24,A2-8/24)

This seems to work fine, but I'm concerned if I missed anything when you stated:
"In order to correctly handle the transition, you will need to know the UTC time for the daylight savings time changes for each time zone."

I don't fully understand what you meant here. I think you may have been alluding to:
From Mar 8 2020 to Nov 1 2020- MST = UTC-6-------PST = UTC-7
From Nov 1 2020 to Mar 14 2021- MST = UTC-7-------PST = UTC-8

Did I get that right?
See the attached excel.

[MrShorty]

You state that the values in column A represent UTC, as that correct? Your formula in column B says that daylight savings time starts at 2 AM on 8 March UTC. If your time zone is 7 hours before UTC, that is equivalent to have DST start at 19:00 on 7 March. If that's what you want, that's fine, but I am used to DST starting at 2 AM local.

Look at rows 3 and 4. The UTC time stamps in A3 and A4 differ by only 33 minutes. In that same 33 minutes, the MST time stamps in B3 to B4 show 6:27 PM and 8:00 PM (1 hour and 33 minutes in prime time). The PST times in C3 and C4 show a similar 1.5 hour jump. If that's your intention, then it is working fine, but I am used to seeing that 1.5 hour jump occur between 1:30 AM local and 3:00 AM local.

I guess what I am trying to say is to be sure that you want the switch to and from daylight savings time to occur at 2 AM UTC or 2 AM local.