Calculate Hours in Excel: Formulas and Time Formats

Formulas & functions · Updated November 20, 2026

To calculate elapsed hours in Excel, put the start time in A2 and end time in B2, then enter =(B2-A2)*24 in C2 and format C2 as a number. For example, 9:00 AM to 5:30 PM returns 8.5 hours. This guide covers same-day shifts, overnight work, and totals, plus the formats and checks that help prevent misleading results.

What You’ll Need

  • An Excel worksheet with start and end times entered in separate cells. Use real Excel time values, such as 9:00 AM, rather than labels or times stored as text.
  • A clear choice about the result you need: decimal hours such as 8.5, or a duration such as 8:30 (8 hours and 30 minutes).
  • For work periods that cross midnight or span multiple dates, the correct date as well as the time for each endpoint.

Step-by-Step

  1. Set up the columns. In a blank worksheet, label A1 Start, B1 End, and C1 Hours. Enter one work period per row, starting in row 2. For example, type 9:00 AM in A2 and 5:30 PM in B2. To enter times, include a colon; Excel will usually recognize entries such as 9:00 AM or 17:30 as time values. You can check by selecting a cell and looking at the formula bar: a recognized time can be displayed in a different format without changing its underlying value.
  2. Calculate the elapsed hours for a same-day period. With a start time in A2 and an end time in B2 on the same day, enter =(B2-A2)*24 in C2. With 9:00 AM and 5:30 PM, Excel calculates 8 hours and 30 minutes, or 8.5 hours. Excel stores one full day as 1 and a time as a fraction of 1, so multiplying the difference by 24 converts days into hours.
  3. Format the answer as a decimal number. Select C2, then use Home > Number Format and choose Number. Set the number of decimal places to 1 or 2 if useful. A result of 8.5 means eight and a half hours; 8.50 is the same quantity displayed with an extra zero. If Excel shows a time instead of a decimal, change the cell’s number format rather than changing the calculation.
  4. Fill the calculation down. Select C2 and drag the small square at the lower-right corner of the cell down through the rows you need. Excel adjusts the references for each row, so the next row uses A3 and B3. Alternatively, copy C2, select the target cells below it, and paste. Check a couple of rows to confirm each result corresponds to the start and end times on that row.
  5. Handle a shift that crosses midnight. If you have clock times only, with the start time in A2 and end time in B2 and the shift is less than 24 hours, enter =MOD(B2-A2,1)*24 in C2. For 10:00 PM to 6:00 AM, this returns 8. The MOD function wraps a negative time difference into the next day. Use this approach only when you know the end time is on the following day; otherwise, a time that is actually earlier on the same day could be treated as an overnight shift by mistake.
  6. Use actual dates for multi-day periods. If a period may last 24 hours or more, enter full date-and-time values in A2 and B2, for example a start date with its time and a later end date with its time. With the full start date and time in A2 and full end date and time in B2, enter =(B2-A2)*24 in C2. A period from 9:00 AM one day to 9:00 AM the next day returns 24. Avoid using the MOD version here, because it discards whole days and returns only the time remainder.
  7. Add a column of durations. You can sum decimal hour results directly, or keep durations as times and convert the sum to decimal hours. With time durations in C2:C4—for example, 8:30, 7:45, and 6:15—enter =SUM(C2:C4)*24 in D2 to return 22.5 hours. The arithmetic is 8.5 + 7.75 + 6.25 = 22.5. Format D2 as Number. If C2:C4 already contains decimal-hour results, use =SUM(C2:C4) in D2 instead; multiplying those values by 24 again would make the total 24 times too large.

If you prefer a duration display instead of decimal hours, subtract the start from the end without multiplying by 24. With the start time in A2 and end time in B2 on the same day, enter =B2-A2 in C2. The example 9:00 AM to 5:30 PM returns a stored duration of 8 hours and 30 minutes. Select C2, press Ctrl+1 on Windows to open Format Cells, choose Custom, and enter [h]:mm in the Type box. On a Mac, Command+1 opens Format Cells. The brackets let accumulated hours display above 24; a standard time format can roll over at 24 hours. You can apply this format to totals as well as individual durations.

Common Mistakes

  • Forgetting to multiply by 24. A simple subtraction returns a fraction of a day, not a decimal number of hours. If a result looks like 0.35, it may represent 8.4 hours. Multiply the difference by 24 when the requested answer is decimal hours.
  • Using a clock-time format for a decimal answer. A correct numeric result can look strange if the cell is formatted as Time. Set a decimal-hours result to Number or General, and use a duration format such as [h]:mm when you want hours and minutes.
  • Entering times as text. An apostrophe before a time, extra spaces, or an imported value Excel does not recognize can prevent subtraction. Re-enter the value as a time or convert the imported data before relying on the result.
  • Using the wrong midnight assumption. MOD treats an end time earlier than a start time as the next day. That is useful for an overnight shift, but can conceal a mistaken entry or a same-day error. Use dates when the actual calendar day matters.
  • Summing hours with a normal clock format. A total such as 27:30 may appear as 3:30 if the cell is formatted to wrap after 24 hours. Apply [h]:mm to the total duration, or convert the total to decimal hours.

Tips

  • Choose one result style for a column and use it consistently. Decimal hours are handy for calculations such as adding or averaging shifts; an hours-and-minutes display is often easier to read as a timesheet.
  • Use actual date-and-time values when shifts can cross midnight, last more than a day, or need to be checked against a calendar. The start and end should each include the appropriate date, not just a clock time.
  • Keep raw start and end entries in their own columns and put formulas in a separate result column. This makes it easier to audit a calculation and correct an entry without overwriting the formula.
  • For a result such as 8.5 hours, remember that the decimal fraction is not minutes: .5 hour is 30 minutes, while .30 hour is 18 minutes. Convert minutes to a fraction of an hour by dividing them by 60.
  • When printing or sharing a timesheet, label the result column clearly as Decimal hours or Duration. This prevents someone from reading 8.5 as 8 hours and 50 minutes.

Troubleshooting

The cell displays ####. Widen the column first by dragging the boundary between its column letters. Hash marks can mean the column is too narrow. If they remain, inspect the inputs and formula: a negative result can also display this way when an end time is earlier than a start time and the formula does not account for an overnight shift.

The result is a small decimal such as 0.354. The subtraction is probably being shown as a fraction of a day. If you need decimal hours, use a formula that multiplies the difference by 24 and set the result cell to Number. If you want hours and minutes, keep the subtraction as a duration and apply the custom format [h]:mm.

The result is much too large or too small. Check whether you multiplied by 24 twice or not at all. A decimal-hour column should be summed as ordinary numbers. A duration column should be summed as time values and shown with [h]:mm; multiply its sum by 24 only when converting that duration to decimal hours.

The formula returns an error or will not calculate. Confirm that the start and end cells contain recognized time or date-and-time values, not text. Click each input and check whether it behaves like a number when its format changes. Also confirm that the formula begins with an equals sign and that the cell is not formatted as Text. If it is, change the format to General, re-enter the formula, and press Enter.

The overnight total is not what you expect. Verify the dates and the intended shift length. The MOD method assumes a single overnight period shorter than 24 hours; it cannot distinguish a shift ending the same day from one ending the next day when only clock times are supplied. Add dates to the inputs for an unambiguous calculation.

Frequently Asked Questions

How do I calculate hours between two times in Excel?
For a same-day period, subtract the start time from the end time and multiply by 24 for decimal hours. If you want a display such as 8:30, subtract the times without multiplying and format the result as [h]:mm.

How do I calculate hours worked overnight?
If the shift crosses midnight and lasts less than 24 hours, use the MOD formula shown above with start and end clock times. For longer periods or a reliable record of the dates, enter full date-and-time values and subtract those instead.

How can I display a total over 24 hours?
Format the duration cell with the custom number format [h]:mm. The square brackets keep the total hours accumulating rather than restarting the display at zero after a day.

How do I turn minutes into decimal hours?
Divide the number of minutes by 60. For example, 45 minutes is 0.75 hour, so a period of 7 hours and 45 minutes is 7.75 decimal hours. If you start with Excel time values, multiplying the duration by 24 performs the conversion.

Can I calculate hours if my cells contain dates and times?
Yes. Enter the full start date and time and the full end date and time, subtract the first from the second, and multiply by 24 for decimal hours. This preserves whole days between the timestamps, unlike a clock-only calculation.

Why does Excel show 8:30 instead of 8.5?
The cell is likely formatted as a time or duration. Change its number format to Number to show decimal hours. If you want 8 hours and 30 minutes, the 8:30 display is appropriate; label the column so its meaning is clear.

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.