How To Get Time Difference In Excel

Alternatively, you can use the MOD function, which automatically wraps the result within a 0‑to‑1 range:

=MOD(B2-A2,1)

Both formulas return the correct fraction of a day; apply a time format to see hours and minutes.

Extracting Hours, Minutes, and Seconds Separately

Sometimes you need the difference broken into individual components—for payroll calculations, charting, or further logic.

Using HOUR, MINUTE, SECOND

Assuming the raw difference is in C2 (=MOD(B2-A2,1)), you can pull out each part:

• Hours: =HOUR(C2)
• Minutes: =MINUTE(C2)
• Seconds: =SECOND(C2)

These functions return integers, so you can combine them into a custom string if desired:

=HOUR(C2) & "h " & MINUTE(C2) & "m " & SECOND(C2) & "s"

Using TEXT for a Custom String

The TEXT function lets you format the raw difference directly into a readable string without extra columns:

=TEXT(MOD(B2-A2,1),"h\"h \"m\"m \"s\"s")

The backslashes escape the literal characters h, m, and s so they appear as labels. Adjust the format string to show only hours and minutes ("h\"h \"m\"m"), or to include days ("d\"d \"h\"h \"m\"m") Easy to understand, harder to ignore..

Displaying Differences Longer Than 24 Hours

Excel’s default time formats reset after 24 hours (showing only the remainder). To accumulate total hours beyond a single day, use a custom format with square brackets That's the part that actually makes a difference..

Steps

1. Calculate the raw difference as before (=MOD(B2-A2,1) or simple subtraction if you know it won’t be negative).
2. Open Format Cells → Custom.
3. Enter one of the following:
   • [h]:mm – shows total hours and minutes (e.g., 25:30 for 25 hours 30 minutes).
   • [h]:mm:ss – includes seconds.
   • dd "days" hh:mm – shows days, hours, and minutes.

The square brackets tell Excel not to wrap the hour component at 24, giving you a true elapsed‑time total.

Practical Examples

Example 1: Regular Work Shift

Example 2: Night Shift

Example 3: Accumulated Weekly Hours

Suppose you have daily totals in E2:E8 (each already formatted as [h]:mm). To get the weekly sum:

=SUM(E2:E8)

Make sure the sum cell also uses the [h]:mm format; otherwise Excel will display only the remainder after 24 hours Simple as that..

Common Pitfalls and How to Avoid Them

Dealing with Negative Time Gaps

When the end time is earlier than the start time (e.g., a night shift that runs past midnight), the simple subtraction B2‑A2 yields a negative result. Because the remainder operation MOD() always returns a non‑negative integer, MOD(B2‑A2,1) would give an error when the difference is negative.

Example: If a task starts at 23:45 and ends at 02:10 the next day, B2‑A2 = -21 h 25 m. Applying ABS(MOD(...)) would return 0.875 h, which is correct because the actual elapsed time is 6 h 25 m. Still, if you need the signed duration (i.e., “‑15 h”), the first conditional approach is preferable.

Combining Fractional Hours with Whole Hours via INTEGER Functions

Often payroll systems require the count of complete hours plus a fractional portion expressed as minutes or seconds. You can achieve this by separating the integer part from the remainder:

=INT(ABS(MOD(B2-A2,1))) & " h" & ROUND(MOD(ABS(B2-A2),1)/60,0)&" m"

• INT(...) discards any fraction of an hour, giving the whole‑hour count.
• ROUND(...,0) converts the leftover fractional hours back into minutes (rounded to the nearest whole minute).
• For sub‑minute precision, replace /60 with /3600 and multiply the resulting seconds by 100 to keep two decimal places.

Building Dynamic Summaries with Array Formulas

If you have many intervals spread across a table (e.g., rows 2‑50) and you want a single “total elapsed time” cell, classic range sums ignore the 24‑hour wrap‑around.

=SUMPRODUCT((B2:B51<>"")*(A2:A51<>"") * 
  ((ABS(MOD(B2:B51-A2:A51,1))-INT(ABS(MOD(B2:B51-A2:A51,1))))/60))

This multiplies each row’s fractional‑hour contribution by its own flag (true only when both dates exist) and adds everything up. The result is a decimal number of hours that automatically respects the >24‑hour behavior thanks to ABS.

For even easier readability, consider converting every interval to a decimal‑hour series first:

=LET(
    frac, ABS(MOD(B2:B51-A2:A51,1)),
    whole, INT(frac),
    SUM(whole + (frac-whole)/60)
)

Using LET (Excel 365/2021+) makes the logic transparent and improves performance on large data sets.

Storing Time Gaps in a Structured Table

When your workbook grows, embedding the calculation inside a structured reference prevents accidental reliance on absolute row numbers. Define a table (Ctrl + T) called ShiftLog:

Below is a practical layout for a shift‑log that can be turned into an Excel structured table (Ctrl + T). The table lets you reference each column by name instead of hard‑coded row numbers, which keeps formulas readable even as the data set grows Not complicated — just consistent. But it adds up..

Computing the duration inside the table

Because the table is named ShiftLog, each column can be addressed as ShiftLog[Start], ShiftLog[End], etc. A compact formula that returns the total number of hours (including fractions) is:

=INT(ABS(MOD([End]-[Start],1))) + (ABS(MOD([End]-[Start],1))-INT(ABS(MOD([End]-[Start],1))))/60

• MOD(...,1) strips the integer part of the difference, leaving a value between 0 and 1 that represents the fractional day.
• ABS guarantees a positive result even when the end time precedes the start time (overnight shifts).
• INT extracts the whole‑hour component, while the division by 60 converts the leftover fraction into hours.

If you prefer minutes instead of decimal hours, use:

=ROUND(ABS(MOD([End]-[Start],1))*1440,0)

Building a dynamic total across the table

When many rows are present, a single “total elapsed time” cell can sum the decimal‑hour column:

=SUM(ShiftLog[Hours]) + SUM(ShiftLog[Minutes])/60

For a more explicit approach that respects the 24‑hour wrap‑around, SUMPRODUCT works well:

=SUMPRODUCT((ShiftLog[End]<>ShiftLog[Start]) *
            (ABS(MOD(ShiftLog[End]-ShiftLog[Start],1))-INT(ABS(MOD(ShiftLog[End]-ShiftLog[Start],1))))/60)

The logical test (ShiftLog[End]<>ShiftLog[Start]) excludes blank rows, while the fractional‑hour expression adds up each valid interval.

Using LET for readability (Excel 365/2021+)

If you have the newer LET function, the same calculation can be expressed in a self‑documenting way:

=LET(
    start, ShiftLog[Start],
    finish, ShiftLog[End],
    diff, ABS(MOD(finish-start,1)),
    whole, INT(diff),
    SUM(whole + (diff-whole)/60)
)

LET assigns names to each intermediate result, making the formula easier to audit and faster to evaluate on large tables.

Handling special cases

• Negative durations – If you need a signed value (e.g., “‑15 h”), drop the ABS wrapper and keep the raw finish‑start difference. The INT part will then reflect the direction of the shift.
• Sub‑minute precision – Replace the /60 step with /3600 and multiply the resulting seconds by 100 (or 1000) to retain two (or three) decimal places.
• Zero‑length entries – The logical test in the SUMPRODUCT example prevents a division‑by‑zero error when a row is incomplete.

Conclusion

By embedding the time‑gap logic inside a structured table, you gain three key advantages. First, column names replace opaque row references, which makes formulas self‑explanatory and resilient to row insertions or deletions. And second, the combination of MOD, ABS, and INT cleanly normalises any length — whether it spans a single day, crosses midnight, or exceeds 24 hours — while still allowing you to extract whole hours and fractional minutes as needed. Now, third, aggregate functions such as SUM or SUMPRODUCT can produce reliable totals without additional tweaking, and the optional LET construct adds clarity for complex calculations. With these tools, payroll, project‑tracking, or any scenario that records elapsed time becomes both accurate and maintainable.