How to Calculate Hours Between Two Dates and Times in Excel

To calculate hours between two dates and times in Excel, subtract the start from the end: =B2-A2. Format the result as [h]:mm to show total hours and minutes, or use =(B2-A2)*24 for decimal hours. Both input cells must contain real Excel date-time values.

Quick Answer

With the start date-time in A2 and the end date-time in B2, choose the result you need:

For example, 6:00 AM on January 20 to 2:00 PM on January 21 is 32 hours. The first formula displays 32:00; the second returns 32.

Download the example workbook to follow the three examples below on the Elapsed Hours sheet: full date-times, separate date and time columns, and an overnight interval with times only.

Calculate Hours Between Full Date-Time Values

Start with these values in row 2. The dates shown here use US month/day/year order; enter them in the order your Excel regional settings expect, and check that they mean January 20 and January 21, 2022.

Enter =B2-A2 in C2. With [h]:mm formatting, you should see 32:00: one full day plus another eight hours. In D2, enter =(B2-A2)*24 and use Number or General formatting to see 32.

Why does subtraction work? Excel stores a date as a serial number and its time as a fraction of a day. The difference here is about 1.333333 days. Multiplying by 24 converts it to hours; formatting the original difference as [h]:mm displays it as a duration. Microsoft's guide to calculating differences between dates and times uses this same subtraction-and-formatting approach.

Keep the dates in the inputs even if you only want hours in the answer. They tell Excel how many midnights have passed.

Show Elapsed Hours Above 24

If C2 shows 8:00, check its number format before changing the formula. A normal h:mm clock format wraps after 24 hours, so it hides the full day in this 32-hour interval.

In desktop Excel:

1. Select C2 and open Format Cells with Ctrl+1 on Windows or Command+1 on Mac.
2. Choose Number → Custom.
3. Enter [h]:mm in the Type box and select OK.

The brackets tell Excel to display accumulated hours. Use [h]:mm:ss when you also need seconds. For more on the display itself, see Let Excel Convert Hours Between Two Dates and Times.

Microsoft's time calculation guide notes that Excel for the web can calculate values above 24 hours but cannot apply a custom number format. If that format isn't available in your browser, apply it in desktop Excel or use the decimal-hours formula with Number formatting.

Calculate Hours When Dates and Times Are in Separate Columns

Add each date to its time before subtracting. On row 6 of the example sheet, an overnight shift runs from March 15, 2026 at 10:00 PM to March 16 at 6:00 AM:

Enter this formula in E6:

=(C6+D6)-(A6+B6)

Format E6 as [h]:mm and you should see 8:00. The first pair of parentheses combines the end date and time; the second combines the start date and time. Subtracting those complete values gives the duration.

To return decimal hours in another cell, use:

=((C6+D6)-(A6+B6))*24

Use Number or General formatting for that result. It returns 8.

Return Decimal Hours, Minutes, or Seconds

For the full date-time example in A2 and B2, multiply the difference by the number of units in one day:

Format these results as Number or General. Microsoft documents these conversions in Calculate the difference between two times.

Decimal hours use fractions of an hour: 8.5 hours means 8 hours and 30 minutes, not 8 hours and 50 minutes. To turn a decimal-hour value back into a duration, divide it by 24 and apply [h]:mm. Our decimal hours to time guide walks through the conversion.

Calculate an Overnight Interval with Times Only

With full date-times, the subtraction you've already used handles midnight. With times alone, Excel has no date to tell it that 6:00 AM belongs to the next morning.

If you know the interval is less than 24 hours, use the MOD function. In row 10 of the workbook, A10 contains 10:00 PM and B10 contains 6:00 AM. Enter this in C10:

=MOD(B10-A10,1)

Format C10 as h:mm to see 8:00. MOD wraps the negative difference into the next day. It also works for a same-day interval when the end time is later than the start time.

The assumption matters: this formula always returns less than one day. It cannot distinguish an 8-hour interval from a 32-hour interval with the same clock times. Identical start and end times return 0:00, even if you meant a full 24 hours. Use full dates whenever those cases are possible.

Don't strip dates out of your inputs before calculating a multi-day duration. Extracting time from a date-time value is useful when you specifically need the time of day, but it removes information this calculation needs.

Fix Text Dates and Regional Date Problems

If subtraction returns #VALUE!, check the inputs first. Imported timestamps can look like dates while still being text. Applying a Date format changes how a number is displayed; it doesn't reliably convert a text string into a date-time value.

Temporarily switch an input cell to General. A real Excel date-time appears as a serial number with a fractional part for its time. If the original date string stays visible, it may be text. Convert the imported values using the source's date order, or re-enter them in a format your Excel recognizes, then check the dates before subtracting.

Be especially careful with values such as 03/04/2026: that can mean March 4 or April 3. Microsoft's date conversion troubleshooting guide explains how a mismatch with system date settings can cause conversion errors. A date interpreted in the wrong order can also produce a plausible but incorrect duration.

Elapsed Hours and Business Hours Are Different

The 32-hour result includes every hour between the two timestamps. It includes overnight time, weekends, and holidays.

The NETWORKDAYS function counts whole working days, excluding Saturdays, Sundays, and an optional holiday list. As Microsoft's NETWORKDAYS documentation makes clear, its result is a day count. Multiplying that count by eight is only appropriate when every counted day represents a full eight-hour workday.

For hours within a schedule, you also need the shift's start and end times, the weekend pattern, holidays, and any partial first or last day. For example, Monday at 4:00 PM to Tuesday at 10:00 AM covers only two scheduled hours on a 9:00 AM–5:00 PM schedule with no breaks or holidays, although the elapsed span is 18 hours. Neither raw subtraction nor a whole-day count calculates those scheduled hours on its own.

Other Common Problems

The result is negative or shows hash marks

Check that the end date-time comes after the start date-time. A negative duration may display as ##### in a time format; a narrow column can also cause hash marks. Widen the column, then check the input order. Use the times-only MOD formula only when its less-than-24-hour assumption fits your data.

The result is a fraction such as 1.333333

That's the duration measured in days. Apply [h]:mm to display hours and minutes, or multiply by 24 and use Number formatting for decimal hours.

Should I use DATEDIF for hours?

Use subtraction for timestamp differences. The DATEDIF function serves date intervals in units such as years, months, and days; it doesn't provide an hours unit.

Can I calculate hours up to the current time?

Yes. With a start date-time in A2, use:

=(NOW()-A2)*24

Format the result as Number. The NOW function updates when Excel recalculates; it isn't a continuously ticking clock or a fixed end timestamp. If you need to record a permanent end time, see how to insert a timestamp in Excel.

Formula Summary

For related calculations, browse the Date & Time tutorials.