Excel Time Math: Formulas for Adding and Subtracting Hours

Formulas & functions · Updated November 18, 2026

Excel time math treats times as fractions of a 24-hour day: subtract an earlier time from a later one with =B2-A2, or add time values with =A2+B2. Format the result as a time; for a shift that crosses midnight, use =MOD(B2-A2,1) to get a positive elapsed duration.

How Excel represents time

Excel stores a time as a decimal fraction of one day. Midnight is 0, noon is 0.5, and 6:00 a.m. is 0.25. A time displayed as 6:00 p.m. is stored as 0.75. The cell’s number format controls how that value looks; it does not change the underlying time.

This explains why ordinary arithmetic works. Subtracting 9:00 a.m. from 5:30 p.m. subtracts the two fractions of a day. Excel calculates 8 hours and 30 minutes, which it can display as 8:30. The displayed value is not the number 8.5 unless you convert it to decimal hours.

Dates use serial numbers too, with each whole number representing a day. When a cell contains both a date and a time, the date is the whole-number portion and the time is the fraction. That makes it possible to subtract full date-and-time stamps to find elapsed time over several days.

Subtract times on the same day

For a start time in A2 and an end time in B2 on the same day, subtract the start from the end. Enter the formula in C2:

With the start time 9:00 a.m. in A2 and the end time 5:30 p.m. in B2, use =B2-A2. The result is 8:30, meaning eight hours and 30 minutes.

Time order matters: use end minus start. If the result appears as a decimal, select the result cell and choose a time format from Home > Number Format. The formula is still working; only the display format differs. To show hours and minutes, select a format such as 13:30.

For a simple workday with an unpaid break, subtract the break duration from the elapsed time. With start time in A2, end time in B2, and break duration in C2 entered as a time such as 0:30, put this in D2: =B2-A2-C2. For 9:00 a.m. to 5:30 p.m. with a 30-minute break, the result is 8:00. If your break is recorded as a number of minutes instead of a time value, use =B2-A2-C2/1440; that assumes C2 contains 30, and it returns 8:00 for the same example.

Add hours, minutes, and durations

To add a duration to a time, add the two cell values. With a meeting time in A2 and a duration in B2, enter =A2+B2. If A2 is 10:15 a.m. and B2 is 1:45, the result is 12:00 p.m. Format the result as a time to make it easy to read.

To add a fixed amount of time directly in a formula, Excel’s TIME function can create a time value. With the starting time in A2, =A2+TIME(0,30,0) adds 30 minutes. If A2 is 10:15 a.m., the result is 10:45 a.m. TIME takes hours, minutes, and seconds as its arguments.

For a list of durations, use SUM. With durations in B2:B6, enter =SUM(B2:B6) in B7. If the five entries total 27 hours and 15 minutes, the underlying result is 1 day plus 3 hours and 15 minutes. A standard time display may show only 3:15, so apply the duration format described below to show the full total.

A useful distinction: TIME is intended to construct a time of day and rolls values over a 24-hour clock. For example, TIME(26,0,0) represents 2:00 a.m., not a 26-hour duration. For a duration entered as a number of hours, divide by 24 instead. With the number of hours in A2, use =A2/24; if A2 contains 26, the result is a 26-hour duration when displayed with [h]:mm.

Calculate time across midnight

A direct subtraction becomes negative when a shift ends after midnight but the cells contain only times of day. For example, subtracting 10:00 p.m. from 6:00 a.m. produces a negative value, because Excel does not know that the second time belongs to the next date. In the usual 1900 date system, negative time values may display as hash marks rather than as a readable time.

For an elapsed interval shorter than 24 hours, use MOD to wrap the difference around the day. With a start time of 10:00 p.m. in A2 and an end time of 6:00 a.m. in B2, enter =MOD(B2-A2,1). The result is 8:00. MOD returns the remainder after division by one day, so the negative difference is brought back to the correct part of the 24-hour cycle.

To calculate the elapsed amount as decimal hours instead of a time display, multiply the duration by 24. With the same overnight times in A2 and B2, enter =MOD(B2-A2,1)*24. The result is 8. This method assumes the interval is less than 24 hours; if a shift might last 24 hours or longer, enter actual dates as well as times and subtract full date-and-time values.

When a date is available, using it is more reliable than guessing whether an end time is on the next day. With a start date and time in A2 and an end date and time in B2, enter =B2-A2. For example, if A2 is November 2 at 10:00 p.m. and B2 is November 3 at 6:00 a.m., the result is 8:00. This also handles intervals spanning multiple days.

Show totals greater than 24 hours

Excel’s standard clock formats cycle through the day. As a result, a sum of 27 hours and 15 minutes can display as 3:15, even though the stored value is correct. Use an elapsed-hours format to stop the hours from restarting at zero.

  1. Select the duration or total cells.
  2. On Windows, press Ctrl+1 to open Format Cells. On Mac, press Command+1.
  3. Choose Number, then Custom, and enter [h]:mm in the Type box. Select OK.

The square brackets tell Excel to show cumulative hours rather than the hour on a 24-hour clock. With this format, a total of 27 hours and 15 minutes appears as 27:15. Use [h]:mm:ss if you also need seconds. This formatting changes only how the value is displayed; it does not alter the calculation.

Convert time to decimal hours or minutes

Decimal hours are useful when a report needs a number such as 8.5 rather than 8:30. Since one day contains 24 hours, multiply an Excel duration by 24. With a duration in A2, enter =A2*24. If A2 contains 8:30, the answer is 8.5 hours. Format the result as Number if Excel displays it as a clock time.

To express a duration as decimal minutes, multiply the time value by 1,440, the number of minutes in a day. With the duration in A2, enter =A2*1440. If A2 is 1:30, the result is 90 minutes. These conversions apply to durations; multiplying a time of day by 24 gives the number of hours since midnight, which is a different measure.

To convert a decimal number of hours back into an Excel duration, divide by 24. With 8.5 in A2, =A2/24 returns the equivalent of 8 hours and 30 minutes. Apply a suitable time or elapsed-hours format to display it clearly.

Formatting and data-entry checks

When a formula seems wrong, check the value type and the number format before changing the arithmetic. A time typed as 8:30 is normally recognized as a time, but text pasted from another system may look identical while remaining text. Text values do not always behave like numeric time values in calculations.

  • Check whether the formula result is formatted as a time, a date, a number, or text. A correct fractional-day result can look surprising under the wrong format.
  • Look for spaces or apostrophes that may make an entered time text rather than a value. Re-entering a sample time directly in Excel can help distinguish an input issue from a formula issue.
  • For totals, use [h]:mm rather than a clock format if the total can reach 24 hours.
  • For overnight calculations, include dates when intervals may be 24 hours or longer. MOD alone cannot tell whether a time-only interval crossed midnight once or several times.
  • Keep units consistent. A cell containing 30 as a plain number means 30, not automatically 30 minutes; convert it with division by 1,440 when necessary.

Regional settings can affect how Excel expects time separators and displays dates, so an imported value may not parse as expected. Excel for Microsoft 365 on Windows and Mac supports the same basic time arithmetic and functions discussed here. Ribbon placement can differ slightly on Mac, but the Format Cells dialog is also available with Command+1. Excel for the web supports ordinary time formulas and cell number formats, though some dialog controls may be arranged differently from the desktop app.

Frequently Asked Questions

Why does Excel show a decimal instead of hours and minutes?
Excel stores time as a fraction of a day, so a result may appear as a decimal if its cell is formatted as General or Number. Apply a time format, or use an elapsed-hours format for totals over 24 hours.

How do I calculate hours between two times when the shift crosses midnight?
For a shift shorter than 24 hours with start time in A2 and end time in B2, use =MOD(B2-A2,1). If the duration might exceed a day, include actual dates and subtract the full date-and-time values.

How do I stop a total from restarting after 24 hours?
Format the result cell with the custom number format [h]:mm. The brackets make Excel display accumulated hours, so 27 hours and 15 minutes shows as 27:15.

How do I turn 8:30 into 8.5 hours?
If 8:30 is a duration in A2, multiply it by 24 with =A2*24, then format the result as a number. The result is 8.5 because 30 minutes is half an hour.

Can Excel calculate a time difference across multiple days?
Yes. Enter both the date and time in the start and end cells, then subtract the earlier date-and-time value from the later one. A time-only formula cannot identify how many whole days passed.

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.