Excel Formula for Hours Between Two Times

To calculate hours between two times in Excel, subtract the start time from the end time and multiply by 24. If the time span crosses midnight, use =MOD(B2-A2,1)*24 instead; the examples below explain when each formula fits and how to display the result.

The basic formula for same-day times

Suppose the start time is in A2 and the end time is in B2. Enter =(B2-A2)*24 in C2. Excel stores times as fractions of a day, so subtraction returns a fraction of a day; multiplying by 24 converts that fraction into decimal hours.

For example, if A2 contains 9:15 AM and B2 contains 5:45 PM, the elapsed time is 8 hours and 30 minutes. The formula returns 8.5, because 30 minutes is half an hour. If the start is 8:00 AM and the end is 12:30 PM, the result is 4.5 hours.

To use it, enter the start and end values as Excel times, put the formula in the result cell, and press Enter. You can fill the formula down a column for additional rows. Excel adjusts the row references automatically, so the next row will calculate from A3 and B3.

How Excel represents time

Excel treats a time as a fraction of a 24-hour day. Midnight is 0, noon is 0.5, and 6:00 PM is 0.75. Subtracting two time values therefore produces a fraction: 30 minutes is 1/48 of a day, while 8 hours is 1/3 of a day. Multiplying by 24 changes that result to hours.

This explains why the simple subtraction alone may look wrong. With a start in A2 and an end in B2, =B2-A2 gives the elapsed time as a fraction of a day. If the result cell is formatted as a number, an eight-hour span appears as about 0.3333, not 8. Multiplying by 24 gives a decimal-hour answer; alternatively, keep the fraction and apply a duration format, as described below.

Excel must recognize the entries as numeric time values for subtraction to work. Values typed as ordinary times, such as 9:15 AM or 17:15, are generally recognized according to the computer’s regional settings. If a value was imported or stored as text, formatting the cell as Time may not convert it; it may need to be re-entered or converted to a real time value.

Calculating a shift that crosses midnight

A straightforward subtraction produces a negative value when the end clock time is earlier than the start clock time. For a shift that begins at night and ends the next morning, with start time in A2 and end time in B2, use =MOD(B2-A2,1)*24. MOD wraps the difference into a fraction of one day, which makes it useful when the entries contain clock times only and the interval is understood to be less than 24 hours.

For example, a shift from 10:30 PM to 6:15 AM lasts 7 hours and 45 minutes. With those clock times in A2 and B2, the formula returns 7.75 hours. The 45 minutes contribute 0.75 of an hour. This formula also works for a same-day interval, such as 9:00 AM to 5:00 PM, and returns 8.

There is an important limit: if the start and end times are identical, MOD returns zero, not 24 hours. It also wraps any interval of 24 hours or more, so it cannot determine whether identical clock times mean no time passed, a full day passed, or multiple days passed. Include dates when that distinction matters. For a span that can exceed one day, use full date-and-time values instead of relying on MOD.

Choose between decimal hours and a duration

Decimal hours are convenient for timesheets, payroll inputs, and calculations that multiply hours by an hourly rate. A result of 7.75 means seven hours and 45 minutes, not seven hours and 75 minutes. Decimal hours are not the same as hours-and-minutes notation, so make the format clear when sharing the value.

If you want the result to display as hours and minutes, with start in A2 and end in B2, use =B2-A2 and format the result cell with the custom number format [h]:mm. In Microsoft Excel for Microsoft 365 on Windows, select the result cell, press Ctrl+1, choose the Number tab, select Custom, and enter [h]:mm in the Type box. In Excel for Mac, Command+1 opens Format Cells; the Number and Custom options are similar. In Excel for the web, use the number-format controls, though the full custom-format options can differ from the desktop app.

The square brackets around h matter. A plain time format such as h:mm displays clock time and resets after 24 hours. The bracketed format shows total elapsed hours, so a 27-hour duration appears as 27:00 rather than 3:00. If you need decimal hours and a duration display, keep separate result cells or change the number format as needed; one cell cannot show both representations at once.

When your cells contain dates as well as times

For actual timestamps, include both the date and time in each cell. If the start date and time are in A2 and the end date and time are in B2, enter =(B2-A2)*24. Excel subtracts the full date-time values, so this works across midnight and across multiple days without MOD. For instance, a timestamp from Monday at 10:30 PM to Tuesday at 6:15 AM gives 7.75 hours.

This approach is safer when a record might span more than a day, or when dates determine which of two identical clock times came first. Make sure the cells contain real date-time values, not labels that merely look like dates. You can show the entries with a date-and-time format while retaining their underlying numeric values. If only clock times are available, the formula cannot infer the date or whether the interval crosses midnight; that information has to come from your data or an explicit rule.

Subtracting a break from the elapsed time

To calculate net hours after an unpaid break, subtract the break duration from the total. If A2 holds the start time, B2 the end time, and C2 the break in decimal hours, use =(B2-A2)*24-C2. For a 9:00 AM to 5:30 PM shift with a 0.5-hour break in C2, the result is 8 hours: 8.5 elapsed hours minus half an hour.

If the break is entered as an Excel time duration rather than decimal hours—for example, 30 minutes in C2—convert that value to hours too. With start in A2, end in B2, and break duration in C2, use =((B2-A2)-C2)*24. The same-day assumption applies to these examples. For an overnight shift, use the MOD-based elapsed time and subtract the break in matching units; for example, if C2 is a time duration, =(MOD(B2-A2,1)-C2)*24 returns net decimal hours, provided the shift is under 24 hours.

Rounding and checking the result

Excel keeps more precision than may be visible on screen. If the answer needs to be rounded to two decimal places, with start time in A2 and end time in B2, use =ROUND((B2-A2)*24,2). A span of 7 hours and 20 minutes is 7.3333… hours, which this formula returns as 7.33. Rounding is different from formatting: showing two decimal places can leave the underlying value unchanged, while ROUND changes the calculated value.

Check the answer in a format that makes sense for the task. For a decimal-hour result, verify a simple example such as 1:00 PM to 2:30 PM, which should be 1.5 hours. For a duration display, verify that 30 minutes appears as 0:30. If the formula shows a negative result, check whether the interval crosses midnight or whether the date values are in the wrong order. If it shows a value like 0.25 when you expected six hours, the result is likely still in days and needs to be multiplied by 24.

Common problems and what they mean

A negative number or hash marks: Basic subtraction can be negative when the end time is earlier than the start time. Use the overnight formula for clock-only values, or include dates. Some Excel date systems do not display negative times as normal clock values, so check the formula and input order rather than assuming the cell is broken.
A decimal that seems too small: Subtraction by itself returns a fraction of a day. Multiply by 24 for decimal hours, or format the value as a duration.
Unexpectedly large hours: If the cells include dates, the result includes every day between them, as intended. If they contain clock times only, check whether a date-time value was entered or whether the cell contains an unexpected date component.
The formula does not calculate: Confirm that the start and end entries are numeric times rather than text, that the formula begins with an equals sign, and that the cell is not formatted as Text. After changing a text-formatted cell, re-enter the formula so Excel evaluates it.
Minutes are mistaken for decimal fractions: 7:30 is 7.5 decimal hours, not 7.30. Convert minutes by dividing them by 60, or let Excel calculate the time difference and multiply by 24.

Frequently Asked Questions

What formula gives hours and minutes instead of decimal hours?

Subtract the start time from the end time, then format the result as [h]:mm. The bracketed hours show the total duration, including spans longer than 24 hours, rather than wrapping around like a clock.

How do I calculate hours between two times when the shift passes midnight?

For clock-only entries in A2 and B2, use =MOD(B2-A2,1)*24 for decimal hours. If the interval might be a full day or longer, enter dates with the times and subtract the complete timestamps instead.

Why does Excel show 0.5 instead of 12 hours?

Excel stores 12 hours as half of a day, or 0.5. Multiply the difference by 24 to get 12 decimal hours, or format the difference as a duration to display 12:00.

Can I calculate a time span longer than 24 hours?

Yes. Use date-and-time values for the start and end, subtract the start from the end, and use [h]:mm to display the total hours and minutes. A clock-time-only formula cannot identify the number of days that passed.

How do I subtract a lunch break?

Subtract the break from the elapsed time. If the break is entered as decimal hours, subtract it after converting the elapsed time to hours; if it is entered as an Excel time duration, subtract it before multiplying by 24.

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.