Excel Time Addition: Formulas, Formatting, and Fixes

Formulas & functions · Updated November 18, 2026

To add times in Excel, enter the values in cells and add them with a formula: if a start time is in A2 and another time or duration is in B2, enter =A2+B2. Format the result as a time; if the total may reach 24 hours or more, use the custom format [h]:mm so Excel displays the full duration instead of restarting at midnight.

How Excel stores and adds time

Excel stores dates and times as numbers. A whole number represents a day in Excel’s date system, while the decimal part represents the fraction of a day. For example, six hours is one quarter of a day, and 12:00 noon is one half of a day. That is why ordinary addition works: Excel adds the underlying numeric values, then displays the result according to the cell’s number format.

Time values can be entered with a colon, such as 8:15 AM or 17:40. Excel recognizes many common time formats based on your Windows regional settings. A value displayed as 8:15 PM is still a numeric value that can be added; a value stored as text is not. This distinction explains why a formula may work for some entries but return an error or an unexpected result for others.

Adding a time of day to a duration is also different from adding two clock readings. If you add 9:00 AM and 2:00 PM as time values, the result is based on their stored fractions of a day, not a meaningful elapsed interval between those clock readings. For work totals, enter durations such as 7:30 and add those. For a start and finish clock time, subtract the start from the finish, with an adjustment for overnight shifts when needed.

Add two times or durations

For two values that should simply be added, use a plus sign. With a duration of 8 hours 45 minutes in A2 and another duration of 7 hours 50 minutes in B2, enter =A2+B2 in C2. The result is 16:35. Copy the formula down to calculate other rows, changing the cell references automatically.

For multiple entries in a column, SUM is convenient. With durations in B2 through B6, enter =SUM(B2:B6) in B7. If those entries are 1:30, 2:15, 0:45, 3:00, and 1:20, the total is 8:50. The range can contain more rows; update the reference to include all the entries you want counted.

When entering a duration, use a colon between hours and minutes. For example, enter 2:30 for two hours and 30 minutes, not 2.30. The latter is a decimal number, equivalent to 2.3 in ordinary number notation, rather than two hours and 30 minutes. If you need to enter seconds, a value such as 2:30:15 represents two hours, 30 minutes, and 15 seconds.

Show totals longer than 24 hours

A standard clock format, such as h:mm, displays a time within a 24-hour day. If a summed duration is 27 hours and 30 minutes, a cell formatted as h:mm may display 3:30 because Excel is showing the clock time after the extra day has elapsed. The underlying result has not necessarily been lost; the display format is wrapping it around midnight.

To display accumulated hours, select the result cells and open Format Cells. On Windows, press Ctrl+1, choose the Number tab, select Custom, and enter [h]:mm in the Type box. The square brackets tell Excel to show total elapsed hours rather than reset the hour count after each day. For a total that includes seconds, use [h]:mm:ss. On a Mac, Command+1 opens Format Cells; in Excel for the web, select the cells and use the Home tab’s number-format controls, noting that available custom-format options can depend on the web interface.

For example, with durations of 9:45 in A2 and 8:30 in B2, enter =A2+B2 in C2. The result is 18:15. Add a further 7:00 in A3 and include all three durations in a sum, and the total is 25:15; the bracketed format displays 25:15, while a standard time format can show 1:15. Use the bracketed format for payroll totals, project effort, or any other elapsed time that can exceed one day.

Add a duration to a starting time

To find when an activity will finish, add its duration to its start time. With a start time of 9:20 AM in A2 and a duration of 3 hours 45 minutes in B2, enter =A2+B2 in C2. Format C2 as h:mm AM/PM or another clock-time format; the result is 1:05 PM. If the duration might extend beyond midnight, the result still advances to the next day internally, but a time-only display shows just the clock time.

If the date matters as well, enter a date and time together in the starting cell, then add the duration. With a date-and-time value in A2 and a duration in B2, use =A2+B2 in C2 and format the result to show both the date and time. This keeps the next day visible when the activity crosses midnight. A format such as m/d/yyyy h:mm AM/PM displays both parts; choose a date order appropriate for your regional settings.

Excel’s TIME function can build a time from separate hour, minute, and second values. With a start date and time in A2, add two hours and 30 minutes by entering =A2+TIME(2,30,0) in B2. TIME is useful when the duration is fixed in a formula or stored in separate components. One important limitation is that TIME returns a time within a 24-hour day: it does not preserve a total such as 27 hours as 27 elapsed hours. For long durations, use a duration cell or a numeric day fraction and display the result with a bracketed hour format.

Calculate elapsed time across midnight

Subtracting a start time from an end time works when both occur on the same day. If the shift starts at 9:00 AM in A2 and ends at 5:30 PM in B2, enter =B2-A2 in C2; the elapsed time is 8:30. Format the result as [h]:mm or h:mm, depending on whether it is a duration or a clock reading.

For a shift that may end the next day, add one day when the end time is earlier than the start time. With the start clock time in A2 and the end clock time in B2, enter =B2-A2+IF(B2<A2,1,0) in C2. For a shift from 10:00 PM to 6:30 AM, the result is 8:30. The comparison checks whether the end is earlier; if it is, the formula adds one full day, represented by 1. This assumes the shift lasts less than 24 hours and that the entered values are valid time values.

If a shift can last more than 24 hours, or if the workbook needs unambiguous dates, include both the start date and end date instead of relying on the earlier-than check. With a full start date and time in A2 and a full end date and time in B2, enter =B2-A2 in C2. Format the result as [h]:mm to show total elapsed hours. This approach also avoids ambiguity for shifts that span several days.

Choose the right number format

The formula calculates a numeric result; formatting controls how that result looks. A cell can contain the correct total and still appear wrong because it is formatted as a date, a decimal, or a clock time that wraps after 24 hours. Check the format before changing a formula.

  • h:mm or h:mm AM/PM: Use for a time of day, such as 4:45 PM, or a duration that is guaranteed to stay below 24 hours.
  • [h]:mm: Use for a duration that could exceed 24 hours. The brackets keep the cumulative hour count.
  • [h]:mm:ss: Use when accumulated seconds matter, such as in a timing log.
  • m/d/yyyy h:mm: Use when the date and clock time both matter, especially for events that cross midnight.

In custom number formats, m can mean month or minute depending on its position in the format. In a time format such as h:mm, the m represents minutes. If a custom format appears to show the wrong component, confirm that the format matches the value you are displaying. You can change a cell’s format without changing the stored time or the calculation.

Common time-addition problems

  • The total looks too small: If a total should be over 24 hours but displays a low clock time, apply [h]:mm. The display may have wrapped even though the underlying total is correct.
  • The formula returns an error: Check whether any supposed time entries are text. Re-enter them as times, or use Data tools to convert imported text where appropriate. An apostrophe typed before a value can force it to be stored as text.
  • The result shows a date or a decimal: Change the result cell’s number format to a time or duration format. A decimal can be a valid underlying fraction of a day, not evidence that addition failed.
  • Minutes appear to be added as decimal fractions: Enter 1:30 for one hour and 30 minutes. Do not enter 1.30 expecting it to mean one hour and 30 minutes.
  • A duration entered with TIME is unexpectedly short: TIME wraps hours into a 24-hour clock. For elapsed amounts longer than a day, use a duration value and a bracketed-hour display format instead.

For imported data, inspect a few source cells and test whether Excel recognizes them as times. Applying a time format to text does not convert the text into a numeric time. A practical check is to add a known valid time to a suspect entry: if Excel cannot calculate with it as expected, the source may need conversion. Be cautious with bulk conversions because date order and regional settings can affect how text is interpreted.

Practical worksheet setup

A clear layout makes it easier to check totals and catch entry mistakes. For a basic work log, use columns for date, start time, end time, and elapsed duration. Keep the date with each shift, especially when work can continue past midnight. Format start and end columns as clock times, and the elapsed column as [h]:mm.

For a same-day work log, with start time in B2 and end time in C2, enter =C2-B2 in D2. This example assumes each shift begins and ends on the same calendar day. For an overnight log using time-only entries, replace that calculation with the overnight formula described above; for more than one day, enter complete dates and times and subtract the start from the end. A separate break-duration column can be subtracted from elapsed time if breaks should not count as work.

To total the duration column, with daily elapsed values in D2:D31, enter =SUM(D2:D31) in D32 and format D32 as [h]:mm. This produces a cumulative duration rather than a clock reading. Consider using data validation or a consistent entry procedure if multiple people maintain the sheet, and verify a few rows manually before relying on a long total.

Frequently Asked Questions

How do I add 30 minutes to a time in Excel?
Enter the starting time in A2 and the duration 0:30 in B2, then put =A2+B2 in C2. Format C2 as a clock time. You can also use a fixed duration in a formula: with the starting time in A2, enter =A2+TIME(0,30,0) in B2.

How do I add hours and minutes from separate cells?
With hours in A2 and minutes in B2, use =A2/24+B2/1440 in C2. Excel represents a day as 1, so hours are divided by 24 and minutes by 1,440. For 2 hours in A2 and 30 minutes in B2, the result is 2:30 when C2 is formatted as [h]:mm.

Why does my Excel time total reset to zero after 24 hours?
The total may be formatted as a time of day, which displays the clock position within a 24-hour cycle. Apply the custom format [h]:mm to show cumulative hours. The formula usually does not need to change.

Can Excel add times with seconds?
Yes. Enter values such as 1:20:45 for one hour, 20 minutes, and 45 seconds, then add them normally. Format the result as h:mm:ss for a clock-like display or [h]:mm:ss for an accumulated duration that may exceed 24 hours.

How do I add a time when the result is on the next day?
Add the duration to the starting date and time, not just to a time-only value, if you need the date of the result. With a full start date and time in A2 and duration in B2, use =A2+B2 and format the result to show the date and time. If you only need the clock reading, a time format is enough, but it will not show which day the result falls on.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

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

Spotted an error in this guide? Let us know.