If the start time is in A2 and the end time is in B2, subtract the start from the end: =B2-A2. Format the result as a duration such as [h]:mm to show total elapsed hours, including totals above 24 hours.
The basic formula for elapsed time
Excel stores times as portions of a day, so subtracting one time from another gives the elapsed time as another portion of a day. For a shift beginning at 9:15 AM and ending at 5:45 PM, enter the start and end times in A2 and B2. The formula =B2-A2 returns 0.3541667 as an underlying value, which Excel can display as 8:30 when the result cell uses a time format.
For a quick one-off calculation, enter the times directly into a formula, such as =5:45 PM-9:15 AM is not valid Excel syntax: time values used inside formulas need to be entered as quoted text or, more reliably, stored in cells. Cell references are easier to audit, reuse, and update. Enter times with AM or PM when needed, or use 24-hour time, such as 17:45.
For the example, select the result cell and apply a duration format. In Microsoft 365 for Windows, press Ctrl+1 to open Format Cells, choose Custom, and enter [h]:mm. On Mac, Command+1 opens Format Cells. The square brackets make Excel show the accumulated number of hours rather than restarting the hour display at each 24-hour boundary.
How Excel stores time
Excel represents a full day as the number 1. Therefore, one hour is 1/24, half an hour is 1/48, and 12 hours is 0.5. A time-only entry such as 6:00 AM is stored as a fraction of a day; a date-time value also includes a whole-number day count. This system is why subtraction works naturally for durations and why multiplying a time difference by 24 converts it to hours.
The stored number and its display format are separate. A result of 0.5 can appear as 12:00 PM when formatted as a clock time, as 12:00 when formatted as hours and minutes, or as 12 when converted to decimal hours. Changing the format does not change the underlying result. It changes only how Excel displays that value.
This distinction matters when a duration looks wrong. If Excel displays 8:30, that usually means eight hours and thirty minutes when the cell has a duration-style format; it does not mean the decimal number 8.5 unless the result has been converted to decimal hours.
Choose a display format for the answer
Use the format that matches how people will read or use the result. The formula can remain the same while the number format changes:
[h]:mmdisplays elapsed hours and minutes and allows the hour total to exceed 24, such as 27:15.h:mmdisplays hours and minutes but cycles the hour display after a full day, so it is suitable for clock-like values rather than cumulative totals.[h]:mm:ssincludes seconds, which is helpful for short intervals or timing data.- A numeric format with decimal places shows the stored fraction of a day, not decimal hours. Multiply the duration by 24 if you want a result such as 8.5 hours.
For a result that totals 27 hours and 15 minutes, [h]:mm displays 27:15. The format h:mm instead displays 3:15 because it shows the time within a 24-hour cycle. That is a display difference, not a change in the elapsed-time calculation.
Calculate shifts that cross midnight
Subtracting the end time from the start time produces a negative value when a shift begins one day and ends the next. For a shift starting at 10:00 PM and ending at 6:30 AM, assuming the start is in A2 and the end is in B2, use =MOD(B2-A2,1). Format the result as [h]:mm; the result is 8:30.
MOD wraps the negative difference into the same 24-hour day, which is useful when all shifts are shorter than 24 hours and the end time earlier than the start time always means the next day. It also works for ordinary same-day shifts: a 9:00 AM start and 5:00 PM end produce 8:00.
There is an important limitation: if the start and end times are identical, this formula returns zero. It cannot determine whether the interval was zero hours or exactly 24 hours from time-only entries. For multi-day work, or when equal times can mean a full day, store the actual dates as well as the times and subtract full date-time values.
Use dates for multi-day durations
If A2 contains a complete starting date and time and B2 contains a complete ending date and time, enter =B2-A2. For example, if A2 is 1 June at 9:00 AM and B2 is 2 June at 11:00 AM, the elapsed duration is 26 hours. Format the result as [h]:mm to display 26:00. Using full date-time values also avoids guessing whether an end time belongs to the next day.
For records that include separate date and time columns, combine the relevant date and time values into date-time values before subtracting, or calculate the difference using references to the combined values. Make sure both entries are real Excel dates and times, not text that merely looks like a date. A quick check is to change a test cell to General: a genuine date-time normally appears as a number, while text remains text.
Show duration as decimal hours or minutes
Some reports need decimal hours for totals or comparisons. With a start time in A2 and an end time in B2, use =(B2-A2)*24. For 9:15 AM to 5:45 PM, the answer is 8.5 hours. Format the result as Number and choose the desired number of decimal places; do not apply an hours-and-minutes format to the decimal result.
For an overnight interval where the start time is in A2 and the end time is in B2, use =MOD(B2-A2,1)*24. A 10:00 PM to 6:30 AM shift returns 8.5 decimal hours. To convert a duration to total minutes instead, multiply the time difference by 1440, since a day contains 24 × 60 minutes. With the same start and end cells, =(B2-A2)*1440 returns 510 for an 8-hour-30-minute interval.
Rounding should reflect the purpose of the calculation. To round a decimal-hour result to two decimal places, use =ROUND((B2-A2)*24,2); the 8-hour-30-minute example returns 8.5. If an organization rounds time to a specific increment, confirm the required policy before building that rule into a worksheet. Formatting to two decimal places changes what is displayed, while ROUND changes the calculated value.
Handle blank cells and common errors
A simple subtraction can show an unexpected result when one input is blank, contains text, or is not the time you intended. If the start time is in A2 and the end time is in B2, this formula leaves the result blank until both cells have values: =IF(OR(A2="",B2=""),"",B2-A2). For overnight shifts shorter than 24 hours, use =IF(OR(A2="",B2=""),"",MOD(B2-A2,1)). Apply [h]:mm to the result cell.
Common symptoms usually point to a small number of causes:
- ##### appears: widen the column first. If the calculation is a negative time difference, check whether the end time is on the following day; use the overnight formula or enter full dates.
- The answer looks like a date or clock time: change the result format to a duration format such as
[h]:mm. - A total above 24 hours appears to restart: replace
h:mmwith[h]:mm. - The result is unexpectedly zero: check for blank inputs, text values, or equal start and end times. Time-only data cannot distinguish a 24-hour interval from no elapsed time.
- The calculation will not update as expected: confirm the cells contain numeric Excel time values and that workbook calculation is not set to Manual.
When typing data, use a consistent convention and check ambiguous entries. For example, 7:00 could mean 7:00 AM or 7:00 PM depending on the data-entry convention. Add AM/PM or use 24-hour time where the distinction matters.
Sum durations across multiple rows
For a list of shifts, calculate each row’s duration and then add the results. If starts are in A2:A10 and ends are in B2:B10, put =B2-A2 in C2 and fill it down through C10 for same-day intervals. For shifts that may cross midnight, use =MOD(B2-A2,1) in C2 and fill down. Format the duration cells as [h]:mm.
If the individual durations are in C2:C10, use =SUM(C2:C10) for the total. Format the total as [h]:mm as well; otherwise, a total greater than one day may display as a clock time that has wrapped around. As a check, three shifts of 8:00, 7:30, and 8:15 total 23:45. If the sum is intended to represent paid hours, account separately for unpaid breaks and any applicable workplace rules rather than assuming the raw elapsed interval is payable time.
Frequently Asked Questions
What is the simplest Excel formula to calculate hours between two times?
If the start is in A2 and the end is in B2, use =B2-A2 and format the result as [h]:mm. Use =MOD(B2-A2,1) when an earlier end time means the shift continued past midnight.
How do I calculate hours worked in Excel as a decimal?
With the start in A2 and end in B2, use =(B2-A2)*24 for a same-day interval. For a shift that crosses midnight, use =MOD(B2-A2,1)*24. Format the result as a number.
Why does Excel show the wrong time after 24 hours?
The usual clock format h:mm repeats its hour display every day. Apply the custom duration format [h]:mm to show accumulated hours, such as 31:20.
How do I calculate a time duration over several days?
Enter both the start and end as complete date-time values, then subtract the earlier value from the later one. Format the result as [h]:mm to display the total hours across days.
Can Excel calculate a full 24-hour duration from two identical times?
Not from time-only entries. Identical times could mean zero hours or a full day, so include the dates or another indicator that identifies the next day.
Related
- Excel Data Model Calculated Field: What to Use Instead
- Excel Data Model: How to Combine Tables and Analyze Them
- Excel Data Model Measures: Add, Format, and Use One
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Large Excel Cheat Sheet: Formulas, Shortcuts & TipsA large Excel cheat sheet is a quick reference for common formulas, keyboard shortcuts, data tools, formatting, and…
- Number Sequence in Excel: Fill Series or Use a FormulaTo create a number sequence in Excel, enter the first two numbers, select both cells, and drag the…
- ABS Function in Excel: Examples and Practical UsesExcel’s ABS function returns the absolute value of a number: its distance from zero, without a negative sign.…
- Box Plot Formula: Quartiles, IQR, and WhiskersA box plot has no single formula: it is built from a five-number summary—minimum, first quartile (Q1), median,…
- Excel Formula for a Future Date: Add Days or MonthsIf the starting date is in A2 and the number of days to add is in B2, enter…
- How to Hide Filter Arrows in Excel and Keep Table FiltersTo hide filter arrows in Excel without removing a table’s AutoFilter setup, click inside the table, open Table…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.