An easy way to convert UTC time to local time

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.

Solution

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.

=A2:A6+(11/24) converts the range to Australian Eastern Daylight Time Zone (AEDT) by adding 11 hours to each.

=A2:A6-(4/24) 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!

For more Excel tips and tricks like this, check out our Video Tutorials page.