Converting UTC to EST or EDT According To HH:MM:SS

**prefnet** · 01-26-2020, 01:39 PM

Hello All,

I have a column of UTC timestamps that look like this: 5/7/2019 8:22:35 AM which I need to convert to UTC -4 or -5 (Eastern Time) depending on whether the date is DST or ST in the United States (2nd Sunday in March - 1st Sunday in November).

I have seen formulas that can determine whether a specific *date* is DST but not whether the actual *time* on that date is DST as well (i.e. times between 12:01 am and 1:59 am on the date in question).

If someone could please help me with a concise formula that would adjust my specific timestamps (down to the seconds) accordingly (-4 if DST, -5 if ST), I would be most grateful.

Thanks!

**BMV** · 01-26-2020, 02:23 PM

I hope it's that you looking for
=A1-(5-AND(A1>14-WEEKDAY(DATE(YEAR(A1),3,1),2)+DATE(YEAR(A1),3,1)+1/12,A1<=7-WEEKDAY(DATE(YEAR(A1),11,1),2)+DATE(YEAR(A1),11,1)+1/12))/24

**prefnet** · 01-26-2020, 02:36 PM

Thanks but that seems to convert DST times to -6, not -4. Also, is there perhaps a shorter way to get the same result?

**BMV** · 01-26-2020, 02:47 PM

Sorry, I don't change + to - . Corrected.

**prefnet** · 01-26-2020, 02:55 PM

Also, I am confused over the application of the proper DST adjustment, does it take place *after* accounting for the UTC offset or before? In other words, do you first apply the then-current UTC -4 or -5 and THEN see whether it is 2:00 am, or apply the new UTC offset of the new time before determining whether it's 2:00 am or not?

**prefnet** · 01-26-2020, 03:56 PM

As I thought, the formula does not quite work right because it assumes that the UTC timestamp at 2:00 am is the cutoff for DST, but in fact it's 2:00 am Eastern Time that matters, not 2:00 am UTC time. So, in other words, the formula first has to deduct the hours to get to Eastern Time, and then figure out whether DST is still on or off.

Can this formula be fixed to accommodate this?

**BMV** · 01-26-2020, 03:57 PM

Probably I miss. 2:00 am local time.
=A1-(5-AND(A1>14-WEEKDAY(DATE(YEAR(A1),3,1),2)+DATE(YEAR(A1),3,1)+7/24,A1<=7-WEEKDAY(DATE(YEAR(A1),11,1),2)+DATE(YEAR(A1),11,1)+1/4))/24

**prefnet** · 01-26-2020, 04:34 PM

I really appreciate your help with this, but it's not quite right.

First, it only changes over to DST in March at 2:00:01, but should really change over exactly at 2:00:00 am
And second, it doesn't seem to be working at all when changing back to ST in November.

Try it out using these two timestamps:
3/8/2020 7:00:00 AM (UTC)
11/1/2020 6:00:00 AM (UTC)

**prefnet** · 01-26-2020, 04:45 PM

If I'm not mistaken, should be:

3/8/2020 7:00:00 AM (UTC) >>> 08/08/2020 03:00:00 AM (UTC -4)
11/1/2020 6:00:00 AM (UTC) >>> 11/01/2020 01:00:00 AM (UTC -5)

**hrlngrv** · 01-26-2020, 05:16 PM

Originally Posted by prefnet

. . . I have a column of UTC timestamps . . . which I need to convert to UTC -4 or -5 (Eastern Time) . . .

IMO, this is a fine time to learn how to use tables and lookups. Create a table like the following.

W1: Eastern
X1: Central
Y1: Mountain
Z1: Pacific

W2: =DATE(2019+INT((ROWS(W$2:W2)-1)/2),3,7+MATCH(1,WEEKDAY(DATE(2019+INT((ROWS(W$2:W2)-1)/2),3,{8;9;10;11;12;13;14})),0))+TIME(7,0,0)
X2: =W2-TIME(1,0,0)

Fill X2 right into Y2:Z2.

W3: =DATE(2019+INT((ROWS(W$2:W2)-1)/2),11,MATCH(1,WEEKDAY(DATE(2019+INT((ROWS(W$2:W2)-1)/2),11,{1;2;3;4;5;6;7})),0))+TIME(6,0,0)

Select X2:Z2 and fill down into X3:Z3. Select W2:Z3, copy, and paste into W4:Z5, W6:Z7, W8:Z9, and W10:Z11.

W12: =-TIME(4,0,0)
X12: =W12-TIME(1,0,0)

Fill X12 right into Y12:Z12.

W13: =-TIME(5,0,0)

Select X12:Z12 and fill down into X13:Z13. Select W12:Z13, copy, and paste into W14:Z15, W16:Z17, W18:Z19, and W20:Z21.

With UTC times in A3:A22, Eastern, Central, Mountain and Pacific, respectively, in B1:E1,

B3: =$A3+LOOKUP($A3,INDEX($W$2:$Z$11,0,MATCH(B$1,$W$1:$Z$1,0)),INDEX($W$12:$Z$21,0,MATCH(B$1,$W$1:$Z$1,0)))

Fill B3 into B3:E22.

This is the general solution. For EDT/EST specifically, with UTC date-time in A3,

=$A3+LOOKUP($A3,$W$2:$W$11,$W$12:$W$21)

The formulas are A LOT LONGER if you try to figure out DST begin <= UTC date-time < ST begin in every formula.

**prefnet** · 01-26-2020, 05:23 PM

Nice, but not what I'm looking for. I have timestamps for many years, so I need the formula to calculate the DST/ST on the fly.

**prefnet** · 01-26-2020, 06:06 PM

Apologies to BMV, the original formula DOES work in November (I had mistyped a cell). Would just like to adjust it slightly so that it correctly changes at 2:00:00 and not 2:00:01 am. Thanks again!

**hrlngrv** · 01-26-2020, 06:22 PM

Originally Posted by prefnet

Nice, but . . . I have timestamps for many years, so I need the formula to calculate the DST/ST on the fly.

Then a UDF written in VBA would be best. If you really, really want a pure formula solution calculating DST/ST on the fly, how far back do your date-time stamps go? US transitioned to the current scheme in 2007. If you have date-time stamps before that, they'd be EST+5EDT,M4.1.0/2,M10.5.0/2 back to 1987, EST+5EDT,M4.5.0/2,M10.5.0/2 back to 1976, and weird before that. Assuming you have no date-time stamps before 1 Jan 2007 07:00:00 UTC, with UTC date-time in A3,

=A3-TIME(IF(AND(DATE(YEAR(A3),3,15-WEEKDAY(DATE(YEAR(A3),3,14)))+TIME(7,0,0) .LE. A3,A3 .LT. DATE(YEAR(A3),11,8-WEEKDAY(DATE(YEAR(A3),11,7)))+TIME(6,0,0)),4,5),0,0)

[I'm on a PC running Linux, and this site believes comparison operators with angle brackets are HTML so croaks. Replace .LE. with the usual less than or equal to operator and .LT. with the less than operator.]

If you do have date-time stamps before 2007, IMO you'd be A LOT BETTER OFF with a table/lookup approach than brute force as above.

**prefnet** · 01-26-2020, 06:28 PM

No, they're all after 2007 and this works perfectly. I'd love a mini tutorial on the breakdown of how the formula works!

**prefnet** · 01-26-2020, 06:32 PM

Also, if there's any way to condense the formula further (I've seen here that these formulas can be made shorter?) that would be great.

**prefnet** · 01-26-2020, 06:53 PM

If I wanted to use a function, then how would I employ it within a cell?

Assuming the code is in a function named "isDST" and results in TRUE if DST and FALSE is not, then something like =if(isDST=True, A3-TIME(4,0,0), A3-TIME(5,0,0))?

Could you help me with this?

**prefnet** · 01-26-2020, 07:30 PM

Needs to be when DST is local time, though, as in the formula...

**hrlngrv** · 01-26-2020, 07:53 PM

Originally Posted by prefnet

Also, if there's any way to condense the formula further (I've seen here that these formulas can be made shorter?) that would be great.

Not that I know of. Maybe there's a trick to come up with the 2nd Sunday in March and 1st Sunday in November which use only 1 DATE and YEAR call each, but I don't know it. Whatever it might be, it WOULD BE obscure to the point of opaque. OK, you could shorten TIME(7,0,0) to "7:00", TIME(6,0,0) to "6:00", and =A3-TIME(...,0,0) to =A3-(...&":00"), but that'd be it.

If you only have date-time stamps for 2007 and later, then my table approach with 200 years should be sufficient for any practical needs. Actually, structuring the table is a lot easier than in #10 above.

W2: 2007-03-11 07:00:00
W3: 2007-11-04 06:00:00
W4: =W2+IF(MOD(DAY(W2)-1,7)<1+COUNT(--("29 Feb "&(YEAR(W2)+1))),371,364)

Fill W4 down as far as needed, e.g., into W5:W389 to handle 2007 through 2200. If you only need Eastern time,

X2: =TIME(5-MOD(ROW()-1,2),0,0)

Fill X2 down to match the formulas in col W. Name this range in cols W and X UTC2ET. Then you could use formulas like

=A3-LOOKUP(A3,UTC2ET)

I believe that's the shortest formula achievable without using single-character range names or UDFs implemented in VBA.

I'd love a mini tutorial on the breakdown of how the formula works!

WEEKDAY(DATE(yyyy,mm,14)) is the weekday number for the 14th of the given month, with Sunday=1 through Saturday=7, so 15-WEEKDAY(DATE(yyyy,mm,14)) is the day of month for the 2nd Sunday in the given month, and 8-WEEKDAY(DATE(yyyy,mm,7)) is the day of the month for the 1st Sunday in the given month. Then DATE(yyyy,3,15-WEEKDAY(DATE(yyyy,3,14)))+"7:00" is UTC when EDT begins in calendar year yyyy, and DATE(yyyy,11,8-WEEKDAY(DATE(yyyy,11,7)))+"6:00" is UTC when EST begins in the Fall of yyyy. The IF call checks whether A3 is on or after the beginning of EDT and strictly before the Fall resumption of EST. If in DST, subtract 4 hours from UTC; otherwise, ST, so subtract 5 hours.

**prefnet** · 01-26-2020, 08:47 PM

Thanks so much!

Now that you've enticed me with a function instead of a formula, how can we incorporate that in a function so that each cell would not have such a long formula?

**prefnet** · 01-26-2020, 08:53 PM

Confused, though, while you use UTC +6 or +7, when the time is already UTC from which we subtract 4 or 5 hours?

**hrlngrv** · 01-26-2020, 09:25 PM

Originally Posted by prefnet

. . . Now that you've entices me with a function, . . .

Very bare bones, only for Eastern Time.

Please Login or Register  to view this content.

Then formulas like

=A3-((5-EDST(A3))&":00")

Slightly more general,

Please Login or Register  to view this content.

=A2-((5-DST(A2,-5))&":00")

Both functions return TRUE/FALSE whether their 1st, UTC, argument is during daylight savings time. The 1st assumes Eastern time zone. The 2nd takes the standard time adjustment from UTC as 2nd argument.

The real trick would be taking UTC and a POSIX time zone spec like EST+5EDT,M3.2.0/2,M11.1.0/2 and returning corresponding local time, but I don't want to reinvent that wheel.

**prefnet** · 01-26-2020, 09:44 PM

So that works great and is much more compact than the long formula but how does it work?
EDST(D2) returns either TRUE or FALSE so...

=D2-((5-FALSE)&":00") or D2-((5-TRUE)&":00") doesn't seem to make sense?

Also, still trying to understand why we add TimeSerial(7, 0, 0) or TimeSerial(6, 0, 0) when the time itself is already UTC?

**BMV** · 01-27-2020, 02:35 AM

oh. i have +3 there -4/-5 :-)
=A3-(5-AND(A3>=14-WEEKDAY(DATE(YEAR(A3);3;1);2)+DATE(YEAR(A3);3;1)+7/24;A3<7-WEEKDAY(DATE(YEAR(A3);11;1);2)+DATE(YEAR(A3);11;1)+6/24))/24

For UDF its better to use Win API because Windows keep all rules.

**hrlngrv** · 01-27-2020, 03:05 AM

Originally Posted by prefnet

. . . =D2-((5-FALSE)&":00") or D2-((5-TRUE)&":00") doesn't seem to make sense?

In arithmetic, Excel automatically converts FALSE to 0 and TRUE to 1. So when either UDF returns FALSE (standard time), the adjustment is 5 hours; else when they return TRUE (daylight savings time), the adjustment is 4 hours.

Also, still trying to understand why we add TimeSerial(7, 0, 0) or TimeSerial(6, 0, 0) when the time itself is already UTC?

Daylight savings time begins at 2:00 AM Eastern Time, which at that particular point in time is 5 hours later on the clock, so 7:00 AM, UTC. If your base is UTC, then midnight to 6:59:59 AM on 8 Mar 2020 is 7 Mar 2020 7:00 PM to 8 Mar 2020 1:59:59 AM EASTERN STANDARD TIME. IOW, daylight savings time in the Eastern time zone begins well into 8 Mar 2020 UTC. Standard time begins again at 2:00 AM, which at that particular time is 4 hours later on the clock, so 6:00 AM UTC. Midnight to 5:59:59 AM on 1 Nov 2020 is 8:00 PM 31 Oct 2020 to 1:59:59 AM EASTERN DAYLIGHT TIME. Standard time in the Eastern time zone begins well into 1 Nov 2020 UTC. IOW, DST/ST in the Eastern time zone do NOT begin at midnight UTC, thus the need to add hours to UTC dates to determine when other time zones change back and forth between daylight savings time and standard time.

**prefnet** · 01-27-2020, 07:21 AM

Hmmm..... still not getting the last part.

If the base time being converted is already UTC, let's say it's 6:00:00 AM on the March morning, then without adding *any* hours to it, we know that 's 1:00:00 AM in New York, or one hour before the clock changes in the Eastern time zone. So, in this case, the adjustment would still be UTC -5 for another hour (as you point out). And the same is the case November, minus 4 hours. So, how does adding 6 or 7 hours to this base time before the conversion (thus making it 12:00:00 PM UTC or 1:00:00 PM UTC) help with this process, and how is it that the function, which then only deducts 4 or 5 hours, make up that difference from what was just added? In other words, you've added 6 or 7 hours to the UTC base time and then only deduct 4 or 5 hours. I'm sure this is very simple, but I'm getting a mind block here.

**BMV** · 01-27-2020, 07:35 AM

prefnet, Look at the my post #23.
1. UTS is always UTS and not depend on date
2. ST for you -5 and its so between November and Mart. So local time 2:00 will be +5 for comparing so 2:00+5:00 = +7:00 its time to change to DST.
3. DST -4 and 2:00+4:00 = +6:00 its time to change to ST.

**hrlngrv** · 01-27-2020, 05:02 PM

Originally Posted by prefnet

. . . let's say it's 6:00:00 AM [UTC] on the March morning [2nd Sunday], then without adding *any* hours to it, we know that 's 1:00:00 AM in New York, . . . the adjustment would still be UTC -5 for another hour . . . So, how does adding 6 or 7 hours to this base time before the conversion (thus making it 12:00:00 PM UTC or 1:00:00 PM UTC) . . .

Your misunderstanding is that I'm adjusting date TO midnight. I'm adjusting UTC dates AWAY FROM midnight UTC. Also, I'm only using the +7 or +6 hours to determine when EDT or EST begins on 2 specific dates. I'm not adding them to the UTC date-time stamp. I'm subtracting 5 or 4 hours from UTC to give EST or EDT, respectively.

I'm working on the assumption that you're STARTING WITH UTC date-time stamps and want to convert them to Eastern Time, either daylight savings or standard. UTC is either 4 or 5 hours ahead of the Eastern time (4 hours ahead of EDT, 5 hours ahead of EST). Daylight saving time starts one second after 01:59:59 AM EST, which becomes 3:00:00 AM EDT, and it ends one second after 01:59:59 AM EDT, which becomes 01:00:00 AM EST (thus there's no 02:mm:ss AM EDT or EST when EDT takes effect in the Spring, both 01:mm:ss AM EDT and 01:mm:ss AM EST when EDT ends in the Fall). The exact time EDT begins is at 07:00:00 AM UTC, and the exact time it ends is 06:00:00 AM UTC.

Adding 6 or 7 hours to a date gives the exact time in UTC for EDT/EST transitions. The hours DON'T MATTER AT ALL on any days other than the 2nd Sunday in March and the 1st Sunday in November, and they only matter on those 2 dates because they give the precise time in UTC at which EDT or EST begins.

In both the formulas and UDFs I've offered, the logic is this:

Eastern_DateTime = Given_UTC_DateTime - [ if ( Presice_UTC_DateTime_EDTstarts <= Given_UTC_DateTime < Presice_UTC_DateTime_ESTstarts ) Then 4 hrs Else 5 hrs ]

Given_UTC_DateTime is adjusted by subtracting 4 hrs during EDT or 5 hrs during EST. The 7 hrs and 6 hrs adjustments are only used in Presice_UTC_DateTime_EDTstarts and Presice_UTC_DateTime_ESTstarts, respectively, in order to adjust the date portion (which is always at midnight, 00:00:00, the way times work in Excel) to the specific time of day in UTC at which EDT or EST begins.

8 Mar 2020 06:59:59 AM UTC is 8 Mar 2020 01:59:59 AM EST EST 5 hours behind UTC
8 Mar 2020 07:00:00 AM UTC is 8 Mar 2020 03:00:00 AM EDT EDT 4 hours behind UTC

1 Nov 2020 05:59:59 AM UTC is 1 Nov 2020 01:59:59 AM EDT EDT 4 hours behind UTC
1 Nov 2020 06:00:00 AM UTC is 1 Nov 2020 01:00:00 AM EST EST 5 hours behind UTC

The Spring transition from EST to EDT happens when clocks in New York would turn 2:00:00 AM, which is 7:00:00 AM in London. The Fall transition from EDT to EST happens when clocks in New York turn 2:00:00 AM, which is 6:00:00 AM in London. UTC times between midnight and 7:00:00 AM on the 2nd Sunday in March are still EST, and UTC times between midnight and 6:00:00 AM on the 1st Sunday in November are still EDT. Adding 7 or 6 hours, respectively, to those two dates (midnight UTC) gives the exact UTC times of day at which EDT and EST transitions occur.

**Roel Jongman** · 01-27-2020, 05:05 PM

small, but crucial typo in the UTC time on 1 nov 2020... adjusted below..

Originally Posted by hrlngrv

8 Mar 2020 06:59:59 AM UTC is 8 Mar 2020 01:59:59 AM EST EST 5 hours behind UTC
8 Mar 2020 07:00:00 AM UTC is 8 Mar 2020 03:00:00 AM EDT EDT 4 hours behind UTC

1 Nov 2020 05:59:59 AM UTC is 1 Nov 2020 01:59:59 AM EDT EDT 4 hours behind UTC
1 Nov 2020 06:00:00 AM UTC is 1 Nov 2020 01:00:00 AM EST EST 5 hours behind UTC
.

**hrlngrv** · 01-27-2020, 05:12 PM

Originally Posted by Roel Jongman

small, but crucial typo in the UTC time on 1 nov 2020 . . .

Fixed in original too. Thanks.

Converting UTC to EST or EDT According To HH:MM:SS

Re: Converting UTC to EST or EDT According To HH:MM:SS

Re: Converting UTC to EST or EDT According To HH:MM:SS

Re: Converting UTC to EST or EDT According To HH:MM:SS

Re: Converting UTC to EST or EDT According To HH:MM:SS

Re: Converting UTC to EST or EDT According To HH:MM:SS

Re: Converting UTC to EST or EDT According To HH:MM:SS

Re: Converting UTC to EST or EDT According To HH:MM:SS

Re: Converting UTC to EST or EDT According To HH:MM:SS

Re: Converting UTC to EST or EDT According To HH:MM:SS

Re: Converting UTC to EST or EDT According To HH:MM:SS

Re: Converting UTC to EST or EDT According To HH:MM:SS

Re: Converting UTC to EST or EDT According To HH:MM:SS

Re: Converting UTC to EST or EDT According To HH:MM:SS

Re: Converting UTC to EST or EDT According To HH:MM:SS

Re: Converting UTC to EST or EDT According To HH:MM:SS

Re: Converting UTC to EST or EDT According To HH:MM:SS

Re: Converting UTC to EST or EDT According To HH:MM:SS

Re: Converting UTC to EST or EDT According To HH:MM:SS

Re: Converting UTC to EST or EDT According To HH:MM:SS

Re: Converting UTC to EST or EDT According To HH:MM:SS

Re: Converting UTC to EST or EDT According To HH:MM:SS

Re: Converting UTC to EST or EDT According To HH:MM:SS

Re: Converting UTC to EST or EDT According To HH:MM:SS

Re: Converting UTC to EST or EDT According To HH:MM:SS

Re: Converting UTC to EST or EDT According To HH:MM:SS

Re: Converting UTC to EST or EDT According To HH:MM:SS

Re: Converting UTC to EST or EDT According To HH:MM:SS

Re: Converting UTC to EST or EDT According To HH:MM:SS

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Converting a Total hour/min/sec from report - not converting to value with substitution

Bookmarks

Bookmarks

Posting Permissions