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.
=๐ฐ๐ธ:๐ฐ๐ผ+(๐ท๐ท/๐ธ๐บ) 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!
For more Excel tips and tricks like this, check out our Video Tutorials page.
Conversation
As you comment, let's keep the discussion friendly and respectful to add value to the conversation.
Lorem ipsum dolor sit amet, consectetur adipiscing elit. Suspendisse varius enim in eros elementum tristique. Duis cursus, mi quis viverra ornare, eros dolor interdum nulla, ut commodo diam libero vitae erat. Aenean faucibus nibh et justo cursus id rutrum lorem imperdiet. Nunc ut sem vitae risus tristique posuere. uis cursus, mi quis viverra ornare, eros dolor interdum nulla, ut commodo diam libero vitae erat. Aenean faucibus nibh et justo cursus id rutrum lorem imperdiet. Nunc ut sem vitae risus tristique posuere.