Excel Daylight Saving Time: How Dates and Hours Work

How-to basics · Updated November 18, 2026

Excel does not automatically apply daylight saving time (DST) rules to a date or time. It stores date-and-time values as numbers without a time zone, so you must account for the relevant location and date yourself—usually by using a known UTC offset or a formula based on that location’s DST rules.

What Excel does with dates and times

Excel represents dates as serial numbers and times as fractions of a day. For example, adding 1 to a date-time value advances it by one day, while adding 1/24 advances it by one hour. The value does not say whether it represents UTC, Eastern Time, or another zone, and Excel does not attach a time-zone identifier or DST rule to it.

This is why two cells that both display 9:00 AM may mean different things: one could be 9:00 AM UTC and the other 9:00 AM in a local zone. Formatting changes how a value looks, not what time zone it represents. A custom number format can show a label such as EST, but that label does not convert the value or prove that the offset is correct.

Functions such as NOW and TODAY use the computer’s current date and clock when Excel calculates them. The operating system may adjust that clock for its configured time zone and DST settings, but the resulting Excel value still has no stored time-zone information. If a workbook is opened on another computer, the same serial value is not automatically converted to that computer’s local time.

Why daylight saving time creates spreadsheet problems

DST changes the offset between a region’s local time and UTC. In many places, clocks move forward in spring and back in autumn, but the dates, transition times, and even the decision to observe DST depend on local law. Rules can also change over time. There is no single formula that correctly handles every country, region, and historical date.

The clock changes also make some local times unusual. When clocks move forward, a range of local times does not occur. When clocks move back, a range occurs twice. For instance, a local appointment recorded as 1:30 AM during the autumn change may refer to either of two different moments. A plain Excel date-time cannot distinguish them.

That distinction matters for flight itineraries, shift records, event logs, and any calculation of real elapsed time. A schedule may be intended to follow local wall-clock time, while a system log usually needs to represent a unique moment. Those are different data requirements and should not be mixed.

Choose a method that matches your data

Before changing a time, identify what the source value means and what the result should mean. A reliable method for one kind of worksheet can give the wrong answer for another.

  • One-time correction: If every value needs the same known one-hour adjustment, add or subtract one hour. This is appropriate only when no transition boundary or regional rule needs to be considered.
  • UTC timestamps: Keep the original UTC values and convert them for display or reporting using the destination zone’s applicable rules.
  • Local schedules: Keep the intended local clock time, but decide how to handle a scheduled time that is skipped or occurs twice during a transition.
  • Elapsed-time calculations: Use UTC start and end timestamps when you need the actual duration between events.

Keep the original timestamp in its own column and put a converted value in another column. This makes the transformation visible, allows you to check it, and avoids losing the source data if a rule or assumption changes.

Apply a fixed one-hour adjustment

If your data needs a simple one-hour change regardless of date, Excel can do it directly. With a date-time value in A2, enter =A2+1/24 to add one hour, or =A2-1/24 to subtract one hour. For example, if A2 contains 10:30 PM on a given date, adding one hour returns 11:30 PM on that date; adding two hours would carry the value into the next date automatically.

You can also use =A2+TIME(1,0,0) for a one-hour increase. Both approaches add elapsed spreadsheet time; neither checks whether a DST transition occurred. A blanket adjustment is therefore not a general DST conversion. It can be wrong for records on transition days, for regions with different rules, and for a set of values spanning both standard and daylight time.

To apply a known fixed difference in hours stored in B2, with the timestamp in A2, use =A2+B2/24. This can be useful for a controlled dataset, but B2 must contain the correct offset for that specific record. An offset that changes by date or location must be determined separately.

Convert UTC to US Eastern Time with a formula

For a concrete example, suppose A2 contains a UTC date-time and you need US Eastern local time for dates from 2007 onward. Under the current US rule, daylight time begins on the second Sunday in March and ends on the first Sunday in November. The following Microsoft 365 formula calculates those transition instants in UTC and applies an offset of UTC−4 during daylight time or UTC−5 otherwise.

With UTC timestamps in A2, enter this in B2 and fill down:

=LET(dt,A2,yr,YEAR(dt),mar,DATE(yr,3,8),nov,DATE(yr,11,1),spring,mar+MOD(1-WEEKDAY(mar,1),7)+7/24,fall,nov+MOD(1-WEEKDAY(nov,1),7)+6/24,dt+IF(AND(dt>=spring,dt<fall),-4/24,-5/24))

For example, a UTC value of July 15, 2026 at 4:00 PM returns noon Eastern Daylight Time. A UTC value of January 15, 2026 at 4:00 PM returns 11:00 AM Eastern Standard Time. Format B2 as a date and time so the converted value is readable. The formula uses LET, available in Microsoft 365 and Excel 2021 or later.

This is a specific example, not a universal time-zone converter. It assumes the modern US transition rule and one Eastern Time location; it is not suitable for dates before 2007 or zones with different rules. Future laws can change, too. For long-lived or compliance-sensitive records, retain UTC and use an authoritative time-zone database or a system designed to apply the required jurisdiction’s rules.

Calculate elapsed time across a clock change

Subtracting local times can produce a misleading duration when the interval crosses a DST transition. If a shift starts at 11:00 PM before the spring clock change and ends at 7:00 AM afterward, the wall-clock difference is eight hours, but only seven actual hours have elapsed in a region that skips one hour. In autumn, the reverse kind of discrepancy can occur because a local hour repeats.

For accurate elapsed time, store each event as a UTC timestamp and subtract the UTC values. With a UTC start in A2 and UTC end in B2, =B2-A2 returns the elapsed duration as a fraction of a day. To display the result as hours and minutes, apply the custom number format [h]:mm; square brackets let the hour count exceed 24. For a numeric hour total, use =(B2-A2)*24. For example, a difference of 7 hours returns 7 from that formula.

If your source records contain only local times, you may not have enough information to calculate an unambiguous duration at the autumn transition. You need to know which occurrence of the repeated hour each timestamp refers to, or have another reference such as a UTC timestamp or recorded offset. A formula cannot recover information that was never recorded.

Common display and data-entry pitfalls

A time that appears wrong may be a formatting issue, a mistaken assumption about the source, or an actual conversion problem. Check what the cells contain before changing them. Formatting a value as a date and time can reveal whether a calculation crossed midnight, while the formula bar can help inspect the underlying value.

  • Adding one hour to every row: This ignores dates when the offset changes and may incorrectly shift records outside the DST period.
  • Typing EST or EDT beside a value: Text labels do not convert the time. They can also become inaccurate when a column includes dates from different seasons.
  • Assuming a displayed time carries a zone: A cell showing 8:00 AM does not reveal whether it is UTC or local time. Record the zone or offset in a separate field.
  • Using a local time for a repeated hour: During the autumn change, the date and clock time alone may not identify a unique instant.
  • Confusing a duration with a clock time: A duration of 27 hours should generally use the format [h]:mm. A regular time format can wrap after 24 hours and display 3:00 instead.

In Microsoft 365 for Windows, use Home > Number Format to choose a date or time category, or open the Format Cells dialog with Ctrl+1 to set a custom format. On Mac, use Command+1 to open Format Cells. These actions change display only; they do not apply DST rules.

Practical alternatives for recurring and shared data

For a list of events in multiple time zones, store at least the UTC timestamp and the intended zone or location as separate fields. Keeping the original local entry as well can help users verify schedules. A numeric offset such as −04:00 is useful for identifying the relationship to UTC at that moment, but an offset alone does not encode future or historical regional rules.

For recurring meetings, store the intended local time and named location rather than repeatedly adding a fixed number of hours to the previous occurrence. A weekly meeting at 9:00 AM in New York is generally intended to remain at 9:00 AM local time even when its UTC equivalent changes. Calendar applications with time-zone support are usually better suited than a basic worksheet to maintaining recurring events through rule changes.

For operational logs, system exports, or data shared across countries, ask the source system to provide UTC or an unambiguous timestamp where possible. Excel remains useful for sorting, filtering, reporting, and arithmetic, but the time-zone conversion should be performed by a process that has the appropriate regional rules when the data requires them.

Frequently Asked Questions

Does Excel automatically change times for daylight saving time?
No. Excel does not apply regional DST rules to stored date-time values. The computer’s clock may change, affecting values returned by NOW when recalculated, but existing timestamps are not automatically converted.

How do I add one hour to a time in Excel?
With the time or date-time in A2, use =A2+1/24 or =A2+TIME(1,0,0). This adds exactly one spreadsheet hour; it does not determine whether DST applies.

Does Excel know whether a time is EST or EDT?
No. Excel stores the numeric date and time, not the zone name or offset. Record that information separately or convert from a clearly identified source such as UTC.

Why is the elapsed time wrong across a DST change?
Local clock time can skip an hour or repeat one. Subtracting local values may therefore differ from the real elapsed time. UTC timestamps provide a consistent basis for that subtraction.

Can Excel convert between time zones?
Excel can add or subtract a known offset, and formulas can implement specified rules. It does not provide a general built-in function that automatically applies every region’s historical and current DST rules.

Related

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.