Excel Time Difference in Hours: Formulas and Examples

To calculate the difference between two Excel times in decimal hours, subtract the start time from the end time and multiply by 24. With a start time in A2 and an end time in B2, enter =(B2-A2)*24; for example, 9:15 AM to 5:45 PM returns 8.5 hours. If you want an elapsed-time display such as 8:30 instead, use the subtraction without multiplying and format the result as elapsed hours and minutes.

Calculate a basic time difference in hours

Put the start time in A2 and the end time in B2. In another cell, enter =(B2-A2)*24. For a start of 9:15 AM and an end of 5:45 PM, the elapsed time is 8 hours and 30 minutes. Excel represents 30 minutes as half an hour, so the formula returns 8.5. Format the result as a number or leave it in General format to see the decimal value.

The order matters: subtract the earlier time from the later time. If you reverse the references, the result is negative. For a list of shifts, place each start and end in the same row, then copy the formula down the results column. Excel adjusts the row references automatically, so the next row calculates from A3 and B3, then A4 and B4, and so on.

Use decimal hours when you need a number for a calculation, such as multiplying hours by an hourly rate. A result of 8.5 means eight and a half hours, not eight hours and five minutes. If a payroll or reporting system expects a particular precision, decide whether to round the value before exporting it; display formatting alone does not change the underlying result.

Why Excel multiplies by 24

Excel stores dates and times as serial numbers. The whole-number part represents days, while a time of day is stored as a fraction of one day. For example, noon is one-half of a day, and 6:00 AM is one-quarter of a day. Subtracting two time values therefore returns a fraction of a day. Multiplying that difference by 24 converts it to hours.

This storage model explains why a subtraction can look like a strange decimal or display as a time rather than as hours. The underlying value may be correct while the cell’s number format is not the one you want. It also means that the same difference can be presented in more than one useful way: as decimal hours for arithmetic, or as hours and minutes for a human-readable duration.

Excel does not attach a time zone to a date-time value. A calculation across a daylight-saving change measures the difference between the stored clock values, not necessarily the actual time elapsed between events in different time zones. For timestamps that cross a daylight-saving transition or involve multiple time zones, use consistently recorded time values, such as UTC, or account for the offset change separately.

Show elapsed time as hours and minutes

If the result should read 8:30 rather than 8.5, keep the result as a fraction of a day. With the start time in A2 and the end time in B2, use =B2-A2. Select the result cell, open Format Cells with Ctrl+1 on Windows or Command+1 on Mac, choose the Custom category, and enter [h]:mm as the format code.

The square brackets are important. A format of h:mm displays the hour component on a clock and cycles after 24 hours. By contrast, [h]:mm displays total elapsed hours, so a duration of 27 hours and 30 minutes appears as 27:30 rather than 3:30. Excel may offer other time formats in the Format Cells dialog, but a custom elapsed-time format is useful whenever the total can exceed one day.

Formatting changes only how a value appears, not its stored value. A cell showing 8:30 still contains a fraction of a day, approximately 0.35417. If you later multiply that value directly by an hourly rate, the result will be scaled as a fraction of a day rather than as a number of hours. Use the decimal-hours calculation for that task, or multiply the elapsed-time value by 24 in the calculation.

Handle shifts that cross midnight

If A2 and B2 contain clock times only and the end time is after midnight, direct subtraction produces a negative value. For a shift beginning at 10:00 PM and ending at 6:00 AM, use =MOD(B2-A2,1)*24. The result is 8 hours. MOD wraps a negative time difference into the remainder of a day, which makes this formula suitable for an overnight shift shorter than 24 hours.

This wraparound approach has a limitation: it assumes the end time belongs to the next day when it is earlier on the clock. If both entries are 8:00 AM, it returns zero, not a 24-hour shift. It also cannot distinguish a shift of 26 hours from a shift of 2 hours when only clock times are supplied. Record the dates as well as the times when the duration might be 24 hours or longer, or when the date of the end time is otherwise uncertain.

For a schedule where every earlier end time means the following day, the MOD formula is a convenient choice. For a table that can include both same-day appointments and overnight shifts, a separate date or an explicit indication of the end date is safer. That removes guesswork and makes the calculation understandable to someone reviewing the workbook later.

Calculate differences using full dates and times

When each entry contains both a date and a time, ordinary subtraction handles midnight and multi-day durations without a special wraparound formula. For example, enter 11/2/2026 10:00 PM in A2 and 11/3/2026 6:00 AM in B2. The difference is eight hours. With the start date and time in A2 and the end date and time in B2, use =(B2-A2)*24 for decimal hours, or use =B2-A2 and apply the [h]:mm format for an elapsed-time display.

Excel must recognize the entries as date-time values, not text that merely looks like a date and time. A quick clue is alignment: numbers and recognized dates generally align to the right by default, while text often aligns to the left. Alignment can be changed manually, so it is only a clue. You can also change a cell temporarily to General format; a recognized date-time becomes a serial number, while text remains text.

Be consistent about including the date for both endpoints. If the end date is accidentally entered as the same day when it should be the following day, Excel correctly calculates a negative duration; the problem is the input, not the subtraction. Full timestamps are especially useful for logs, equipment use, travel, and any record in which an event may last longer than one day.

Choose decimal hours or an elapsed-time display

Decimal hours and hours-and-minutes displays answer different practical needs. Decimal hours are easier to add, average, multiply by a rate, or send to a system that expects numeric hours. A duration of 2 hours 45 minutes is 2.75 decimal hours, because 45 minutes is 45 divided by 60 of an hour. It is not 2.45 hours.

Elapsed-time formatting is often easier to read in schedules and service logs. A displayed value of 2:45 clearly means two hours and 45 minutes, but it is not the decimal number 2.45. When totals are being compared, make sure every value uses the same representation. Mixing decimal hours with time-formatted fractions can lead to totals that appear plausible but are mathematically wrong.

You can convert a numeric number of hours to an Excel time value by dividing it by 24. With a decimal-hours value in A2, enter =A2/24 in another cell and format that result as [h]:mm. If A2 contains 8.5, the result displays as 8:30. Keep the original decimal value if you also need to calculate pay or other amounts based on hours.

Round results without losing useful precision

Excel may show many decimal places even when a result is correct. If you only want a cleaner display, select the result and use the decimal-place controls in the Home tab’s Number group. That changes the display, not the stored value. This is useful when a later calculation should retain the full precision of the original time difference.

If the actual value must be rounded to two decimal places, with start and end times in A2 and B2, use =ROUND((B2-A2)*24,2). A difference of 8 hours and 31 minutes is about 8.5167 hours, so this formula returns 8.52. Rounding to two decimal places is not the same as rounding to the nearest minute: hundredths of an hour are fractions of 60 minutes, and 0.01 hour equals 0.6 minutes.

For a process that requires rounding to the nearest whole minute, calculate in minutes and divide back to an Excel time value. With the start time in A2 and the end time in B2, use =ROUND((B2-A2)*1440,0)/1440, then format the result as [h]:mm. There are 1,440 minutes in a day, so this rounds the elapsed value to the nearest minute. Apply the rounding rule required by the relevant workplace or reporting policy rather than assuming every use case follows the same rule.

Common problems and how to fix them

The result looks like a time of day. The result cell may have a time format. For decimal hours, choose General or Number. For an elapsed duration, use the custom format [h]:mm.
The result is negative for an overnight shift. Clock-only entries do not say which date the end belongs to. Use the MOD formula for a shift under 24 hours that crosses midnight, or enter full dates and times.
The answer is much too small. A raw subtraction is measured in days, not hours. Multiply by 24 for decimal hours, or apply an elapsed-time format if you want a time-style display.
The formula does not calculate. One or both inputs may be text, or the formula cell may itself be formatted as Text. Enter valid Excel times, change the result cell to General or Number as appropriate, and re-enter the formula. A leading apostrophe can also cause an entry to be treated as text.
A total resets after 24 hours. Use [h]:mm rather than h:mm when the result is an elapsed duration. The unbracketed hour format cycles through the clock’s hours.
Blank rows show zero. A basic subtraction treats blank cells like zero in many arithmetic contexts, so a formula copied down a table may display an unwanted value. Keep incomplete rows out of the calculation or add a deliberate blank-checking rule suited to the table.

Also check the source data for accidental date changes, inconsistent AM/PM entries, and values that were pasted as text. A formula cannot infer whether 7:00 means morning or evening if the entry is ambiguous. For recurring reports, use clear column headings such as Start date/time, End date/time, and Hours, and apply the same input and output formats to every row.

Frequently Asked Questions

How do I calculate the number of hours between two times in Excel?
Subtract the start time from the end time and multiply by 24. If the start is in A2 and the end is in B2, enter =(B2-A2)*24. The formula returns decimal hours.

How do I calculate hours worked overnight?
For clock-only times in A2 and B2, where an earlier end time means the shift continued into the next day, use =MOD(B2-A2,1)*24. For longer or date-sensitive shifts, include the date with each time and subtract the full date-time values.

How do I show hours and minutes instead of a decimal?
Subtract the start from the end, then format the result with the custom format [h]:mm. For example, 8 hours and 30 minutes displays as 8:30. The brackets allow totals above 24 hours to remain cumulative.

Why does Excel show 0.5 for a 12-hour difference?
A time subtraction returns a fraction of a day. Half a day is 12 hours, so the raw difference is 0.5. Multiply the difference by 24 to convert it to 12 decimal hours.

Is 2:30 the same as 2.30 hours?
No. In an elapsed-time display, 2:30 means two hours and 30 minutes, or 2.5 decimal hours. The decimal number 2.30 means 2.3 hours, which is 2 hours and 18 minutes.

Can Excel calculate hours across multiple days?
Yes. Enter the date and time for both the start and end, subtract the full values, and multiply by 24 for decimal hours. For a readable duration, subtract without multiplying and format the result as [h]:mm.

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.