Converting EST to UTC

[mgc8cc]

Hey all,

Looking to see if anyone has a formula to convert a Date/Time in EST to UTC. The original date looks as:

202002141653

This would be 2/14/2020 at 4:53PM EST. Is there a formula to convert this to UTC and take into account the Date as well? It should be:

202002142153

I can only find formulas to convert UTC to Local Time.

[MrShorty]

I can only find formulas to convert UTC to Local Time.

Because the conversion form UTC to local is a simple addition/subtraction, then the conversion from local to UTC is the inverse subtraction/addition. So, according to https://earthsky.org/astronomy-essen...universal-time converting UTC to EST is to subtract 5 hours (EST=UTC-5). Solve for UTC to get the conversion for EST to UTC (UTC=EST+5)

Are you intending to convert these to Excel recognizable and usable date/time serial numbers, or are you going to leave the date/times in this "integer" format? Assuming these date/time integers never include seconds and always include two digits for the minute, then you could conceivably add 500 to the EST number to get the UTC number. 202002141653+500=202002142153. That's going to fail, though when you get later in the evening and you need the time to cross midnight into the following morning. 202002142053+500 is not going to be 202002150153.

If you want these to be date/time serial numbers that Excel can really use, you need to convert them to Excel's date/time serial number system (number of days since Jan 0 1900 -- see here if you are unfamiliar with Excel's calender system: http://www.cpearson.com/Excel/datetime.htm ). In this case, treat the original number as a text string, parse out year, month, and day and use a DATE() function to return the date part of the date/time serial number. Then parse out hour and minute and use the TIME() function to get the time part of the date/time serial number, then add them together. Then you can add 5 hours by adding 5/24 to the result or adding TIME(5,0,0).

Date =DATE(LEFT(date string,4),mid(date string,5,2),mid(date string,7,2))
Time of day =TIME(MID(date string,9,2),RIGHT(date string,2),0)
Add them together to get the complete date/time serial number =DATE(...)+TIME(...)
Then convert to UTC =DATE(...)+TIME(...)+TIME(5,0,0)

As explained, this will return some value in the 40000s (feb 14 2020 is serial number 43875).

Will something like that work for you?

[ben_hensel]

I mean I would force it from the pseudo-ISO8601 integer, to an Excel date-time Double so we can just have Excel add five hours like this:

Please Login or Register  to view this content.

And then format that to display Date-Time the way you want.

But that's assuming we're OK going with Excel-native ways of looking at things and you're comfortable that wherever you're pulling this input from will always be robustly machine-perfect.

Well if you need to go back to an ISO-ish format (for export or whatever?) I would probably leave that as an intermediate cell and then just concatenate that into a third "output" column.

So if the above was in B3, in C3 your actual output would be:

Please Login or Register  to view this content.

That's what I'd do, like generally here.

[mgc8cc]

Thank you! Exactly what I am looking for. The date/time functions are hard for me, as ive never used them.

[mgc8cc]

Thanks! Put me in the right direction. This helped a lot.

[Rick Rothstein]

Here is another formula to add 5 hours to that "date" value...

=TEXT(A1,"0000-00-00 00\:00")+5/24

You would then format this cell with the date/time format of your choosing.