Convert UTC to local time in Excel

Dates and times are two of the most frustrating data types to deal with. You have differing formats, conventions, leap years, and time zones to think about. And because these vary depending on where you are in the world, it only adds to the confusion.

Many downloadable datasets on the web use Universal Coordinated Time (UTC), the primary time standard time zones are based on.

As UTC is fixed, it doesn't change throughout the year as many time zones do, so the advantage is obvious: less hassle.

The issue is these datasets often assume users know what format the dates and times are in by not labelling the columns explicitly.

Some people lack familiarity with the standard, so they can easily fall into the trap of thinking the values are based on their current location.

This becomes especially important in scenarios where date and time data is a fundamental part of the analysis process.

In any case, you can insert an additional column that converts the UTC values to a local or another time zone for convenience.

𝗦𝗼𝗹𝘂𝘁𝗶𝗼𝗻

The calculation is:

(UTC date/time) +/- (Hours offset ÷ 24)

In the video example, A2:A6 contains a set of dates and times in UTC format.

=𝙰𝟸:𝙰𝟼+(𝟷𝟷/𝟸𝟺) converts the range to Australian Eastern Daylight Time Zone (AEDT) by adding 11 hours to each.

=𝙰𝟸:𝙰𝟼-(𝟺/𝟸𝟺) converts the range to Eastern Daylight Time (EDT) by taking away four hours from each.

Always be sure you know what time zone your dataset uses!

#exceleration #excel #microsoftexcel #excelformulas #exceltips #exceltricks #globalexcelsummit

---

The Global Excel Summit is the world's largest gathering of Microsoft Excel users and experts.

Find us on:

[wifipiv]

It was really a great support, thanks for the tutorial.

[luscagabriel]

You helped me a lot, thank you so much! I might even save your video

[Upasini-pccc]

Thank you, this was a perfect solution

[kishi]

you are THE F GOAT MATE! thanks alot !!

[jerrydellasala]

What would have been MUCH MORE USEFUL was having a sample workbook with the VBA code to create a Defined Name that specified at least the UTC offset, and maybe even the UTC Minutes offset so that one could just add a value to the UTC time instead of having to first find out what the UTC offset is (I know the Eastern offset is either -4 or -5 but can never remember which is for what).