Recommended Free Tools
Calculating the difference between two times in Excel is straightforward once you understand how Excel stores time values. Since times are treated as fractions of a day, subtracting a start time from an end time can give you the elapsed duration, whether you need it displayed as hours and minutes or converted into a decimal value.
This is useful for timesheets, project tracking, shift schedules, response times, and any worksheet where you need to measure elapsed time accurately. The main details to get right are the cell formatting, the formula used, and how to handle cases where the end time falls after midnight.
As an Amazon Associate I earn from qualifying purchases.
Calculate Time Difference with Simple Subtraction
The simplest way to calculate a time difference in Excel is to subtract the start time from the end time. Excel stores times as fractions of a day, so 6:00 AM is stored as 0.25, 12:00 PM as 0.5, and 6:00 PM as 0.75. Because of this, regular subtraction works as long as both times are on the same date or within the same 24-hour period.
The Tool Desk
Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →For example, if cell A2 contains the start time 9:00 AM and cell B2 contains the end time 5:30 PM, enter this formula in C2:
=B2-A2
Excel will return the elapsed time between the two values. In this example, the result is 8:30, meaning 8 hours and 30 minutes. If the result appears as a decimal such as 0.3541667, the formula is still working; the cell just needs to be formatted as a time duration.
Basic example
| Start Time | End Time | Formula | Result |
|---|---|---|---|
| 9:00 AM | 5:30 PM | =B2-A2 | 8:30 |
| 1:15 PM | 4:45 PM | =B3-A3 | 3:30 |
| 8:10 AM | 11:25 AM | =B4-A4 | 3:15 |
To make this work reliably, enter your times in a format Excel recognizes, such as 9:00 AM, 17:30, or 5:30 PM. Avoid typing extra text in the same cell, such as Start 9:00 AM, because Excel may treat the entry as text instead of a usable time value.
If you have many rows of start and end times, enter the subtraction formula once, then copy it down the column. For instance, if your start times are in column A and end times are in column B, place =B2-A2 in C2 and drag the fill handle down. Each row will calculate its own elapsed time using the matching start and end values.
Do these 3 things before closing this tab:
1Clear out junk files and repair common Windows errors2Fix the driver behind crashes, sound loss and screen glitches3Repair Windows errors before they cause bigger problems- Start time: the earlier time, such as clock-in or task start.
- End time: the later time, such as clock-out or task finish.
- Difference: the elapsed time returned by subtracting start from end.
This basic subtraction method is best for same-day calculations, such as tracking meeting length, work sessions, production time, or response time. If the end time is after midnight, such as a shift from 10:00 PM to 6:00 AM, simple subtraction may return a negative value or display hash marks. That case needs a slightly different formula, covered later with overnight time calculations.
Format the Result as Hours, Minutes, or Seconds
After subtracting the start time from the end time, Excel returns a time serial value. For example, if A2 contains 9:00 AM and B2 contains 5:30 PM, the formula =B2-A2 returns 8:30 when formatted as a time duration. If the result looks like a decimal such as 0.354166667, the calculation is still correct; the cell just needs a time-based number format.
To show the elapsed time as hours and minutes, select the result cell, press Ctrl + 1 to open Format Cells, choose Custom, and enter h:mm. This displays a result like 8:30. If a duration can exceed 24 hours, use [h]:mm instead. The square brackets tell Excel to show total elapsed hours rather than rolling over after 24 hours. For example, a 27-hour difference appears as 27:00 with [h]:mm, but may display as 3:00 with a regular time format.
Useful custom formats for time differences
| Format | Displays | Example result |
|---|---|---|
| h:mm | Hours and minutes, best for durations under 24 hours | 8:30 |
| [h]:mm | Total hours and minutes, including durations over 24 hours | 27:15 |
| h:mm:ss | Hours, minutes, and seconds | 8:30:45 |
| [m] | Total elapsed minutes | 510 |
| [s] | Total elapsed seconds | 30600 |
Use [m] when you want the full duration expressed only in minutes. For instance, an 8-hour 30-minute difference displays as 510. Use [s] when you need total seconds, such as for call duration, process timing, or exported system logs. If you use mm instead of [m], Excel shows only the minute portion within the hour, so 8:30 would display as 30, not 510.
Quick wins for a faster PC:
Clear out junk files and repair common Windows errorsFree Scan →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Repair Windows errors before they cause bigger problemsFix Now →You can also apply these formats from the ribbon. Select the result cells, go to the Home tab, open the Number Format drop-down, and choose More Number Formats. In the Custom category, type the format you need. This is especially useful when formatting an entire column of time differences, such as shift lengths, task durations, or service response times.
Calculate Time Difference Across Midnight
Simple subtraction works when the end time is later on the same day, but it can return a negative value or a row of hash symbols when the time period crosses midnight. For example, if a shift starts at 10:00 PM and ends at 6:00 AM, the end time is technically smaller than the start time in Excel’s time system. Excel stores times as fractions of a day, so 10:00 PM is greater than 6:00 AM unless you tell Excel that the end time belongs to the next day.
The most common fix is to add 1 day when the end time is earlier than the start time. If the start time is in A2 and the end time is in B2, use this formula:
=IF(B2<A2,B2+1-A2,B2-A2)
This formula checks whether the end time is less than the start time. If it is, Excel adds one full day to the end time before subtracting. If not, it uses normal subtraction. With 10:00 PM in A2 and 6:00 AM in B2, the result is 8:00 when the result cell is formatted as a duration, such as [h]:mm.
Example: overnight shift calculation
| Start Time | End Time | Formula | Result |
|---|---|---|---|
| 10:00 PM | 6:00 AM | =IF(B2<A2,B2+1-A2,B2-A2) | 8:00 |
| 11:30 PM | 2:15 AM | =IF(B3<A3,B3+1-A3,B3-A3) | 2:45 |
| 8:00 PM | 8:00 AM | =IF(B4<A4,B4+1-A4,B4-A4) | 12:00 |
You can also use the MOD function to make the formula shorter. With the start time in A2 and the end time in B2, enter:
=MOD(B2-A2,1)
This returns the positive time difference within a 24-hour day. If B2 minus A2 is negative, MOD wraps the result around to the next day. For instance, 6:00 AM – 10:00 PM produces a negative value with direct subtraction, but =MOD(B2-A2,1) returns 8:00. Format the result cell as [h]:mm if you want to display elapsed hours and minutes reliably.
The MOD formula is convenient for time intervals that are always less than 24 hours. If you need to track shifts or tasks that may last longer than one full day, include dates with the times instead. For example, use 1/15/2026 10:00 PM as the start and 1/16/2026 6:00 AM as the end. Then simple subtraction, such as =B2-A2, works correctly because Excel can see the actual date change.
After entering any overnight formula, check the number format of the result cell. Use [h]:mm for total hours and minutes, [h]:mm:ss for hours, minutes, and seconds, or mully the result by 24 if you need decimal hours. Without the right format, Excel may show a time of day instead of the elapsed duration.
Convert Time Difference to Decimal Hours or Minutes
Excel stores time as a fraction of a day, so a duration such as 6 hours is stored as 0.25, because 6 hours is one quarter of a 24-hour day. That storage method is useful for date and time calculations, but it can be confusing when you need a decimal result for payroll, billing, timesheets, productivity reports, or data analysis. To convert a time difference into decimal hours or decimal minutes, mully the time value by the correct number of units in a day.
Convert a time difference to decimal hours
If your start time is in A2 and your end time is in B2, first calculate the time difference with subtraction. For a same-day time span, use:
=(B2-A2)*24
For example, if A2 contains 9:15 AM and B2 contains 5:45 PM, the time difference is 8 hours and 30 minutes. Mullying by 24 returns 8.5, which is the decimal-hour version of 8 hours 30 minutes. This is usually the format needed for hourly rates, invoices, and payroll calculations.
Convert a time difference to decimal minutes or seconds
To convert the same duration into minutes, mully by 1440, because there are 1,440 minutes in a day. To convert it into seconds, multiply by 86400, because there are 86,400 seconds in a day.
- Decimal hours: =(B2-A2)*24
- Decimal minutes: =(B2-A2)*1440
- Decimal seconds: =(B2-A2)*86400
Using the same example of 9:15 AM to 5:45 PM, the decimal-minute formula returns 510, because 8 hours 30 minutes equals 510 minutes. The decimal-second formula returns 30600.
Handle overnight time differences
If the time range can pass midnight, use the MOD function before converting the result. This prevents negative values when the end time is earlier than the start time. For example, if a shift starts at 10:00 PM in A2 and ends at 6:30 AM in B2, use:
=MOD(B2-A2,1)*24
This returns 8.5 decimal hours. For minutes and seconds across midnight, use =MOD(B2-A2,1)*1440 or =MOD(B2-A2,1)*86400. The 1 in the formula represents one full day in Excel’s date-time system.
Format decimal results correctly
After entering one of these formulas, format the result cell as Number, not Time. If the cell is formatted as Time, Excel may display a value such as 8:30 instead of 8.5. Select the result cells, open Format Cells, choose Number, and set the desired number of decimal places. For payroll, two decimal places is common, so 8 hours 30 minutes appears as 8.50.
Outdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware matchPC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11| Result needed | Formula | Example output |
|---|---|---|
| Decimal hours | =(B2-A2)*24 | 8.5 |
| Decimal minutes | =(B2-A2)*1440 | 510 |
| Decimal seconds | =(B2-A2)*86400 | 30600 |
| Decimal hours across midnight | =MOD(B2-A2,1)*24 | 8.5 |
Use TEXT and MOD Functions for Cleaner Results
The TEXT and MOD functions are useful when you want time difference results that are easier to read or more reliable across different shift patterns. Simple subtraction works well when the end time is later than the start time on the same day, but formulas can become harder to interpret when a shift crosses midnight or when the result needs to appear as a clean label such as 8 hours 30 minutes.
If your start time is in A2 and your end time is in B2, you can combine MOD with subtraction to handle both same-day and overnight time differences:
=MOD(B2-A2,1)
This formula returns the elapsed time as a fraction of a day. For example, if A2 contains 10:00 PM and B2 contains 6:00 AM, normal subtraction gives a negative value because Excel sees 6:00 AM as earlier than 10:00 PM. The MOD formula wraps the result into the next day and returns 8:00 when the cell is formatted as a time duration.
Display the result with TEXT
The TEXT function converts the calculated duration into a formatted text result. This is helpful when you want a consistent display without changing the cell’s number format manually. For hours and minutes, use:
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
=TEXT(MOD(B2-A2,1),”h:mm”)
Using the same example, this displays 8:00. If you want leading zeros, use:
=TEXT(MOD(B2-A2,1),”hh:mm”)
This displays results such as 08:00 or 01:30, which can look cleaner in reports, timesheets, and exported summaries.
Show hours, minutes, and seconds
To include seconds in the result, use a format code with seconds included:
=TEXT(MOD(B2-A2,1),”h:mm:ss”)
For example, if A2 is 9:15:20 AM and B2 is 1:45:50 PM, the result displays as 4:30:30. This is useful for tracking task durations, call times, machine runs, or any activity where seconds matter.
Create a more descriptive result
You can also use TEXT to return a sentence-like duration. For example:
=TEXT(MOD(B2-A2,1),”h”)&” hours “&TEXT(MOD(B2-A2,1),”mm”)&” minutes”
If the elapsed time is 8 hours and 30 minutes, the formula returns 8 hours 30 minutes. This kind of output is easier for people to read, especially in dashboards or shared worksheets where users may not understand Excel time formatting.
- Elapsed time as duration: =MOD(B2-A2,1), then format the cell as [h]:mm.
- Clean hours and minutes text: =TEXT(MOD(B2-A2,1),”h:mm”).
- Clean hours, minutes, and seconds text: =TEXT(MOD(B2-A2,1),”h:mm:ss”).
- Readable label: combine TEXT with & to add words such as hours and minutes.
Use MOD when the calculation must work even if the end time is after midnight. Use TEXT when the result is meant for display rather than further calculation. If you need to total several time differences later, keep the numeric MOD result in one column and apply formatting there, because TEXT results are stored as text and are not ideal for arithmetic.
Free tools Windows power users keep installed
One-click scans. No signup required.
Fix Common Time Difference Errors in Excel
Most time difference problems in Excel come from formatting, negative time values, or times that Excel is treating as text instead of real time values. Since Excel stores time as a fraction of a day, a valid time such as 6:00 AM is stored as 0.25, and 12:00 PM is stored as 0.5. If a formula looks correct but the result is confusing, check both the formula and the cell format before changing the calculation.
Negative time results appear as #####
If Excel shows ##### after subtracting one time from another, the result may be negative or the column may simply be too narrow. First, widen the column. If the hashes remain, the calculation is probably producing a negative time, such as 9:00 PM - 6:00 AM when the end time is on the next day but no date is included. For overnight shifts, use a formula such as =MOD(B2-A2,1), where A2 contains the start time and B2 contains the end time. This returns the elapsed time correctly even when the end time is after midnight.
Times are stored as text
A time that looks normal may still be stored as text, especially if it was imported from another system. Text times do not calculate reliably. To test this, select the cell and look at the formula bar, or use =ISNUMBER(A2). A real Excel time returns TRUE; a text value returns FALSE. You can often convert text times by selecting the cells, going to Data > Text to Columns, and clicking Finish. Another option is to use =TIMEVALUE(A2), then format the result as a time.
- Problem: 8:30 AM is left-aligned and will not calculate. Fix: convert it with =TIMEVALUE(A2).
- Problem: the result shows 0.375 instead of 9:00. Fix: apply the custom format [h]:mm.
- Problem: the result shows a clock time instead of elapsed hours. Fix: multiply by 24 for decimal hours.
Elapsed time is formatted incorrectly
If a shift lasted 27 hours and Excel displays 3:00, the cell is probably formatted as h:mm, which resets after 24 hours. Use [h]:mm instead. The square brackets tell Excel to show total elapsed hours, not the hour portion of a clock. For total minutes, use [m]; for total seconds, use [s]. These formats are especially useful for timesheets, production logs, call durations, and service-level reports.
| Error | Likely Cause | Fix |
|---|---|---|
| ##### | Negative time or narrow column | Widen the column or use =MOD(end-start,1) |
| 0.25 instead of 6:00 | General or Number format | Format the cell as h:mm or [h]:mm |
| Incorrect overnight result | End time is earlier than start time | Use =MOD(B2-A2,1) |
| Formula returns an error | Time stored as text | Convert with TIMEVALUE or Text to Columns |
Also check for hidden dates when working with imported timestamps. A cell may display only 9:00 AM but actually contain a full date and time, such as 1/15/2026 9:00 AM. In that case, simple subtraction usually works well, but formatting the result as [h]:mm is still needed for totals over 24 hours. If you only want the time portion, use =MOD(A2,1) to strip out the date and keep the time value.
Frequently Asked Questions
How do I calculate the hours between two times in Excel?
Enter the start time in one cell and the end time in another, then subtract the start time from the end time, such as =B2-A2. Format the result as [h]:mm if you want Excel to show elapsed hours and minutes correctly, especially when the total can exceed 24 hours.
Why does my time difference show as a decimal instead of hours and minutes?
Excel stores time as a fraction of a day, so a result like 0.5 means 12 hours. To display it as time, change the cell format to h:mm or [h]:mm. If you want decimal hours instead, mully the time difference by 24.
How do I calculate time worked when the shift goes past midnight?
Use the MOD function to handle overnight times, such as =MOD(B2-A2,1), where A2 is the start time and B2 is the end time. This prevents negative time errors when the end time is earlier than the start time, such as 10:00 PM to 6:00 AM.
Do these 3 things before closing this tab:
1Clear out junk files and repair common Windows errors2Fix the driver behind crashes, sound loss and screen glitches3Repair Windows errors before they cause bigger problemsHow do I convert a time difference into total minutes or seconds?
To get total minutes, subtract the start time from the end time and mully by 1440, such as =(B2-A2)*1440. To get total seconds, multiply by 86400 instead. For overnight shifts, use =MOD(B2-A2,1)*1440 or =MOD(B2-A2,1)*86400.
Why am I seeing #### or a negative time error in Excel?
The #### display often means the column is too narrow, so widening the column may fix it. If the result is a negative time, the end time may be earlier than the start time because the time range crosses midnight. Use =MOD(end_time-start_time,1) to calculate the elapsed time correctly.
Bottom Line
Calculating time difference in Excel usually starts with a simple subtraction formula, then depends on choosing the right number format for hours, minutes, or seconds. For shifts that pass midnight, use a formula such as adding 1 day when the end time is earlier than the start time, or use MOD to keep the result clean.
If your answer looks wrong, check that your entries are real Excel times, not text, and apply the correct custom format like [h]:mm for totals over 24 hours. Once the formatting is right, you can reuse the same formulas for timesheets, schedules, elapsed time tracking, and overnight work calculations.
Outdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware matchPC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
