Excel Time Zones: How Date and Time Conversion Works

Excel does not assign a time zone to a date or time value: a worksheet cell holds a date-and-time serial number, not a time-zone-aware timestamp. To convert a timestamp using a known offset, add or subtract the offset in hours divided by 24; for example, a UTC time in A2 and an offset in hours in B2 can be shifted with =A2+B2/24.

What Excel means by a date and time

Excel represents dates as numbers and times as fractions of a day. The whole-number portion counts days from a starting date system, while the decimal portion represents the time within a day. For instance, 0.5 represents noon, and a value with a fractional portion of 0.75 represents 6 p.m. A cell’s date or time appearance comes from its number format; formatting does not add a time zone.

This is why a cell showing 9:00 AM cannot tell you whether that means 9:00 AM in London, New York, or somewhere else. Excel stores neither a location such as America/New_York nor the offset that applied at that moment. If you send a workbook to someone in another time zone, the stored value generally remains the same; Excel does not automatically reinterpret or shift it to the recipient’s local time.

Excel workbooks can also use either the 1900 or 1904 date system, which changes the serial number associated with a calendar date. That is a separate compatibility issue, not a time-zone setting. Excel generally accounts for the workbook’s selected date system when displaying its dates, but copying raw serial values between workbooks with different systems can produce dates shifted by several years.

Convert a timestamp when the offset is known

For a one-time conversion, first establish what the source time represents and the offset that applies to the target time at that date. Use a full date and time rather than a time-only value so that Excel can correctly represent a conversion that crosses midnight.

With the UTC date and time in A2 and the target UTC offset in hours in B2, use =A2+B2/24. Enter negative offsets with a minus sign: UTC−5 is -5.
For example, if A2 contains January 15, 2026 at 15:00 and B2 contains -5, the result is January 15, 2026 at 10:00. Five hours is 5/24 of a day, so subtracting it gives the correct local clock time.
With a local date and time in A2 and the local offset from UTC in hours in B2, convert to UTC with =A2-B2/24. For example, 10:00 at UTC−5 becomes 15:00 UTC on the same date.

Offsets do not have to be whole hours. A location at UTC+5:30 can be represented as 5.5 in the offset cell; UTC+5:45 is 5.75. Set the result cell to a date-and-time number format if it displays as a decimal or an unexpected time. The calculation changes the serial value, while number formatting controls how that value looks.

Why an offset is not the same as a time zone

An offset such as UTC−5 says how far a clock is from UTC at a particular moment. A named time zone, such as America/New_York, describes a set of rules that can change over time, including daylight saving transitions and historical rule changes. The same named zone can therefore have different offsets on different dates, and different zones can share an offset for part of the year.

A formula that adds five hours or subtracts five hours applies that offset every time. It does not look up a location’s rules, determine whether daylight saving time is in effect, or infer a zone from a city name. This matters even when a fixed offset gives the right answer for a sample row: it may be wrong for rows from another season or year.

If the source contains an ISO 8601 timestamp with an explicit offset, such as a date and time followed by +02:00, preserve that offset when interpreting the source. Do not treat the timestamp as UTC merely because it is written in a familiar date-and-time format. If you need to retain the original meaning for auditing or later conversion, keep separate columns for the original timestamp, its offset or zone identifier, and the converted value.

Daylight saving time and tricky dates

Daylight saving makes named-zone conversion more complicated than adding a constant. In a zone that changes clocks seasonally, the correct offset depends on the timestamp’s date and the relevant jurisdiction’s rules. Those rules can be revised, and they are not identical in every country or region. Excel worksheet date serials do not include the rule database needed to resolve them automatically.

Clock changes also create unusual local times. When clocks move forward, some local clock times do not occur. When clocks move back, a range of local times occurs twice. For example, a local entry of 1:30 a.m. during a fall-back transition may refer to either of two different UTC instants. Without an offset or another distinguishing detail, Excel cannot know which one was intended.

For recurring reports, avoid hard-coding one offset for an entire year if the source and destination zones observe different clock rules. Better options include storing UTC as the consistent reference, using an authoritative conversion service or maintained transition table, or handling the conversion in a system that supports named time-zone rules. If a business process requires a specific interpretation of ambiguous times, document that policy rather than silently guessing.

Using Power Query or another conversion source

For repeated imports, Power Query can help organize and transform timestamp data. In Excel for Microsoft 365 on Windows, a common starting point is Data > Get Data > From File > From Text/CSV. In the Power Query Editor, inspect the detected type and the source format before loading the results. Automatic type detection can misread dates when a file uses a different regional order, such as day/month/year instead of month/day/year.

Power Query has date-time-zone data types and functions for working with UTC and explicit offsets. These are useful when a source already supplies an offset or when a query needs to apply a known offset. They do not, by themselves, turn a city or named zone into a complete historical daylight-saving rule set. A fixed-offset change is not a substitute for looking up the applicable rules for each date.

When loading results to a worksheet, do not assume the sheet cell will preserve all the zone information carried in a query value. Keep the original text or an explicit zone/offset column if that information matters after loading. Refreshing a query can update imported values, but it does not make ordinary worksheet date serials time-zone-aware. Excel for Mac and Excel for the web may offer different query editing and data-source options, so the exact import workflow can vary; the underlying limitation of worksheet date values remains.

NOW, UTC, and timestamps from other systems

The worksheet function NOW returns the current date and time according to the computer’s system clock. In normal use, that means the local clock configured in the operating system, not a UTC timestamp with zone metadata. NOW recalculates when Excel recalculates the workbook; it is not a continuously ticking clock, and changing a computer’s time-zone setting does not retroactively convert previously stored timestamps.

Excel has no built-in worksheet function named UTCNOW. If a workbook needs a reliable current UTC time, obtain it from a source that explicitly supplies UTC, such as a trusted data service, or use a controlled Power Query or automation process. Clearly label the source and refresh time. A manually entered offset may be suitable for a simple fixed-offset calculation, but it is not a dependable way to generate UTC across daylight-saving changes.

When importing logs, database exports, or CSV files, preserve the original timestamp before transforming it. A text value that includes a Z suffix or an offset may be safer to inspect as text initially, especially if Excel’s regional settings might interpret its date order differently. Convert only after confirming whether the source is UTC, local time, or an offset-bearing value.

Common mistakes and ways to avoid them

Assuming the workbook has a time-zone setting: Excel’s date-system setting is about serial dates, not location or daylight saving. Label time columns explicitly, such as UTC timestamp or local time with offset.
Using the wrong sign: To convert UTC to UTC−5, add -5/24; to convert UTC−5 to UTC, subtract -5/24, which adds five hours. Test one known example before filling a formula down.
Storing only a clock time: A time-only value cannot show whether a conversion crossed into the previous or following date. Keep the date with the time for calculations.
Confusing display with conversion: Changing a cell’s number format changes its appearance, not its instant. A cell formatted to show only hours and minutes can hide a date change or an incorrect underlying value.
Relying on abbreviations: Labels such as CST can refer to different zones. Prefer UTC offsets plus a documented zone identifier when the location’s rules matter.
Ignoring the import locale: Check ambiguous dates and confirm the column’s type before transforming or loading it. A wrongly parsed date will remain wrong after an otherwise correct offset calculation.

Frequently Asked Questions

Does Excel automatically adjust times for daylight saving?
No. A worksheet date-and-time value has no named time zone, so Excel does not apply daylight-saving rules to it. Use a method with the correct zone rules or a maintained offset table when automatic seasonal conversion is required.

How do I convert UTC to local time in Excel?
If you know the applicable offset, add that signed number of hours divided by 24 to the UTC value. For changing offsets, determine the correct offset for each timestamp first; a single fixed offset will not cover every daylight-saving period.

Does Excel have a UTCNOW function?
No built-in worksheet function called UTCNOW is available. NOW uses the computer’s system clock, typically its local time. Use a trusted UTC source or a managed data-import process when a UTC timestamp is required.

Will a workbook change times when someone opens it in another country?
Ordinary date-and-time serial values do not automatically shift to the viewer’s local time. A recipient may see a different display if formats or regional settings differ, but formatting alone is not a time-zone conversion.

Can I convert a time zone by changing the cell format?
No. Formats control how a number appears; they cannot apply an offset or determine daylight-saving rules. Perform the conversion in the value or in a data transformation, then format the result for readability.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.