To calculate hours between a start time and an end time, put the start in A2 and end in B2, then enter =B2-A2 in C2. Format C2 as [h]:mm to show elapsed hours and minutes, including totals over 24 hours. This guide covers that basic calculation, decimal hours, overnight shifts, and adding multiple time entries.
What You’ll Need
- A worksheet with start and end times entered as real Excel time values, not text that only looks like a time. For example, enter 9:15 AM and 1:00 PM into separate cells.
- A blank column for the calculation and, if you are totaling several shifts, a cell for the sum.
- Microsoft Excel for Microsoft 365 on Windows. The formulas below also work in current Excel for Mac and Excel for the web; some formatting and keyboard steps differ slightly.
For a basic elapsed-time calculation, separate dates are not required if every shift starts and ends on the same day. For work that crosses midnight, use the overnight method below. If you have the actual dates as well as times, entering full date-and-time values is the clearest option.
Step-by-Step
-
Set up columns for the times. Put a heading such as Start in A1, End in B1, and Hours Worked in C1. Enter each start time in column A and its matching end time in column B, with one shift per row. For example, a shift from 9:15 AM to 1:00 PM belongs in A2 and B2. You can type a time with AM or PM, or enter a 24-hour time such as 13:00. Avoid typing labels like 9am shift into the time cells, because extra words can make the value text rather than a time.
-
Subtract the start time from the end time. With the start time in A2 and the same-day end time in B2, enter
=B2-A2in C2. Excel stores times as fractions of a day, so subtracting one time from another returns a duration. For 9:15 AM to 1:00 PM, the result is 3 hours and 45 minutes. If C2 shows a decimal or a clock time instead, that is usually a display-format issue, not a problem with the subtraction. -
Format the result as elapsed hours and minutes. Select C2, then press Ctrl+1 on Windows to open Format Cells. On a Mac, use Command+1. Choose Custom in the Category list, enter
[h]:mmin the Type box, and select OK. The square brackets make Excel display total elapsed hours instead of restarting the hour count at 24. A duration of 3 hours and 45 minutes appears as 3:45. The format changes how the value looks; it does not change the calculation. -
Copy the calculation to other rows. Select C2 and drag the small fill handle at the lower-right corner down the column, or copy C2 and paste it into the cells below. Excel adjusts the row references automatically: the next row will use A3 and B3. Check that each result lines up with the corresponding start and end times. If the same formula is needed for many rows, converting the range to a table with Insert > Table can make formulas fill down as new rows are added.
-
Calculate hours as a decimal when needed. Decimal hours are useful for timesheets or calculations that expect values such as 3.75 rather than 3:45. With the start time in A2 and end time in B2, enter
=(B2-A2)*24in C2. The result for 9:15 AM to 1:00 PM is 3.75, because 45 minutes is three-quarters of an hour. Format the result as Number, and choose the number of decimal places you want. Do not apply the[h]:mmformat to this decimal result, because that format is intended for an Excel time duration. -
Handle a shift that passes midnight. A plain subtraction can return a negative time when the end time is earlier on the clock than the start time. If A2 contains a start time of 10:00 PM and B2 contains an end time of 6:00 AM, use this formula in C2:
=MOD(B2-A2,1). It returns an elapsed duration of 8:00 when C2 is formatted as[h]:mm. To get decimal hours instead, with the same A2 and B2 layout, use=MOD(B2-A2,1)*24; the result is 8. If a shift can last more than 24 hours, enter full dates and times and subtract the date-time values instead. MOD wraps the result into a single day, so it is not suitable for longer durations. -
Add several shift durations. If each row’s calculated duration is in C2:C7, enter
=SUM(C2:C7)in C8. Format C8 as[h]:mm. For example, if C2:C6 each contains 8:00 and C7 contains 1:30, the total is 41:30. Using the ordinaryh:mmformat can make that same value appear as 17:30 because standard clock formatting wraps at 24 hours. If C2:C7 contains decimal-hour results instead, sum those numbers directly; five 8-hour shifts and one 1.5-hour shift total 41.5. -
Choose the display that suits your report. Use
[h]:mmto show a duration in hours and minutes, such as 41:30. Use a decimal formula when a downstream calculation requires numeric hours, such as 41.5. Keep the underlying duration values consistent within a column. Mixing decimal values and time serial values in the same sum will produce misleading results because Excel interprets them on different scales.
Common Mistakes
Using the wrong format for a total. Excel may display a sum of 27 hours as 3:00 if the cell is formatted as a clock time. The stored total can still be correct. Apply the custom [h]:mm format to cumulative durations so the hours do not reset after a day.
Forgetting that time is stored as part of a day. A result of 0.5 from subtracting times means half a day, or 12 hours. Multiply a duration by 24 to express it as decimal hours. Do not multiply a result that is already decimal hours by 24 again.
Entering times as text. An apostrophe before a time, extra words, or an import from another system can cause Excel to treat a value as text. If subtraction returns an error or behaves unexpectedly, select the input and check whether Excel recognizes it as a time. Re-enter it as a valid time, or use Data > Text to Columns if you need to convert imported text values carefully.
Using plain subtraction for overnight work. If a shift starts at 10:00 PM and ends at 6:00 AM, =B2-A2 treats the end as earlier on the same day. Use the MOD formula for a time-only entry, or enter both dates and times so that the calendar day is explicit.
Confusing duration with a time of day. A result formatted as 3:45 could mean 3 hours and 45 minutes when it is a duration, or 3:45 on a clock when it is a time of day. Use a duration column heading and the bracketed hour format for elapsed time so the meaning is clear.
Tips
Enter full date-and-time values for attendance or project logs when shifts might span midnight or multiple days. With a start date and time in A2 and an end date and time in B2, the same subtraction works: enter =B2-A2 in C2 and format the result as [h]:mm. For example, a start at 10:00 PM on Monday and an end at 6:00 AM on Tuesday returns 8:00. The date portion removes the ambiguity about which day the end time belongs to.
Keep raw inputs, calculated durations, and decimal-hour conversions in separate columns. This makes it easier to audit a result and avoids overwriting the times someone entered. Consider adding clear headings such as Start, End, Duration, and Decimal Hours. If you print or share the sheet, include units in the headings so readers know whether 8.5 means hours or something else.
For a decimal output rounded to two decimal places, you can use cell formatting rather than rounding the underlying value. With A2 as the start time and B2 as the end time, the formula =(B2-A2)*24 produces decimal hours; format the cell as Number with two decimal places. This preserves the calculation’s precision for later sums. If you deliberately need a rounded value for a specific report, remember that rounding each shift before summing can give a slightly different total from summing first and rounding the final result.
Use consistent time-entry conventions. Excel can understand both 1:00 PM and 13:00, but mixing formats can make a sheet harder to review. For overnight work, decide whether users will enter time only and use MOD, or enter complete date-and-time values. Date-and-time entry is generally more reliable when shifts could run for a full day or longer.
Troubleshooting
The result displays as ####. The column may be too narrow, or the result may be a negative time under the workbook’s current date system. Widen the column first. If the shift crosses midnight, confirm that you used the MOD formula for time-only entries or supplied the correct end date for full date-time entries.
The total looks 24 hours too small. Select the total cell and apply the custom [h]:mm format through Ctrl+1 on Windows or Command+1 on Mac. The standard hour format shows a clock-style time and wraps after 23:59; the bracketed format shows accumulated hours.
The formula returns an error or zero. Check that the start and end cells contain recognized times, and make sure the formula points to the correct row. Click each input cell and inspect the formula bar. If imported values are text, convert or re-enter them as times before calculating. Also check that the result cell is not stored as text; change its format to General or the appropriate time format and re-enter the formula if necessary.
The answer is a decimal when you expected hours and minutes. A duration of 0.25 represents six hours because Excel stores a day as 1. Format a regular duration result as [h]:mm, or multiply the time difference by 24 if you specifically want decimal hours. Confirm which representation your report expects.
An overnight total is much too long or too short. MOD assumes the end time is on the following day whenever the clock time is earlier than the start time. That is right for a typical overnight shift, but not if the end actually belongs to the same date or the interval exceeds 24 hours. In those cases, enter date-and-time values and subtract them directly.
Frequently Asked Questions
How do I calculate hours between two times in Excel?
Put the start time in A2 and end time in B2, then enter =B2-A2 in C2. Format C2 as [h]:mm to display the elapsed hours and minutes. This basic subtraction assumes the end is later on the same day.
How do I calculate hours worked as a decimal?
With the start time in A2 and end time in B2, enter =(B2-A2)*24. Format the result as Number. A duration of 7 hours and 30 minutes becomes 7.5 decimal hours.
How do I count hours for an overnight shift?
With a start time in A2 and end time in B2, use =MOD(B2-A2,1) and format the result as [h]:mm. This treats an earlier end time as occurring after midnight. If the shift may exceed one day, enter the dates as well as the times and subtract the full date-time values.
Why does my total reset after 24 hours?
The total cell is probably formatted as a clock time with h:mm. Change its custom number format to [h]:mm so Excel displays accumulated hours, such as 32:15, rather than wrapping to 8:15.
Can I add decimal hours and Excel time values together?
Not directly without converting one type. Time durations are stored as fractions of a day, while decimal hours use hours as whole-number units. Convert the time durations to decimal hours by multiplying by 24, or keep all values as durations and display totals with [h]:mm.
Related
- Color Heat Rank in Excel with Conditional Formatting
- How to Color Cells in Excel Based on Values Automatically
- How to Color Negative Values in Excel Automatically
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Flip Names in Excel in the Same Cell With a FormulaTo flip a name such as John Smith to Smith John in Excel for Microsoft 365, enter a…
- How to Sort and Filter a Table in Excel Step by StepTo sort and filter an Excel table, select a cell in it and use the arrow in a…
- Excel Workdays Between Dates: Formulas and ExamplesTo count workdays between two dates in Excel, use NETWORKDAYS. With the start date in A2 and the…
- Flash Fill in Excel: Is There a Formula or Shortcut?Flash Fill in Excel is not a formula: it detects a pattern in your example and fills matching…
- Sort Excel Rows by Column Value: A Practical GuideTo sort rows in Excel by a column value, click a cell in the data, then choose Data…
- AVERAGE vs AVERAGEIF in Excel: Which Should You Use?Use AVERAGE to find the mean of numeric values in a range; use AVERAGEIF to average only values…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.