How To Calculate Time In Excel | Subtract, Add & Format

To calculate time in Excel, subtract the start time from the end time using a simple formula, then format the result with a custom code like [h]:mm to keep totals from rolling over after 24 hours.

Excel handles time as a fraction of a day—1:00 PM is stored as 0.54167, not as a clock display. That makes raw subtraction easy, but you’ll get a decimal unless you tell Excel to show it as hours and minutes. The steps below walk through the core formulas, the formatting that prevents surprises, and how to handle night shifts that cross midnight.

Calculate Time Differences in Excel: The Core Formulas

The workhorse formula is =end_time - start_time. Enter both times in cells (for example, 9:00 AM in A2 and 5:00 PM in B2), then type =B2-A2 in another cell. If the result looks like 0.33333, that’s still correct—the cell is just showing the raw decimal. Apply a time format (Home > Number Format > Time) and it will display as 8:00 AM (meaning 8 hours). For totals that can exceed 24 hours, skip the standard time format and use a custom code instead (see the next section).

The table below lists the most common time formulas and what they return.

For the official step-by-step rundown, Microsoft’s Add or subtract time article explains the same formulas with worked examples.

What Format Prevents the 24‑Hour Rollover?

If you sum several time values—say, 10 hours + 15 hours—Excel’s standard h:mm format wraps the total back to 1:00, showing only 1 hour instead of 25. The fix is a custom format that tells Excel to keep accumulating. Right-click the result cell, choose Format Cells > Custom, and type [h]:mm;@ in the Type box. Now 25 hours displays as 25:00. The bracket [ ] around h is the critical part—it forces hours beyond 24 to show rather than reset.

After you apply a custom format, the cell value stays a decimal—Excel just draws it differently. To verify, click the cell and look at the formula bar: you’ll still see the raw decimal.

Handling Times That Cross Midnight

A simple =B2-A2 breaks when the end time is on the next day—for example, starting at 11:00 PM and ending at 1:00 AM. Excel sees the end as smaller than the start and may show a negative time (######) or an incorrect difference. The cleanest fix is to include the date with each time. Enter 1/15/2026 11:00 PM and 1/16/2026 1:00 AM; subtraction then yields the correct 2 hours. If you can’t add dates, use the =MOD(end-start,1) workaround: it wraps the difference back into a positive fraction. For example, =MOD(B2-A2,1) with times-only cells returns 0.08333, which formats as 2:00 AM. That 2:00 is actually 2 hours—the AM just comes from the format. To display it as a length of time, apply the [h]:mm custom format afterwards.

Summary: Quick Steps for Time Calculations

• Enter times as numeric values. Type 9:00 AM or 13:30—Excel stores them as fractions of a day.
• Subtract for elapsed time: =end-start. Then format the result cell as Time or a custom code.
• Get decimal hours/minutes/seconds: multiply the difference by 24 (hours), 1440 (minutes), or 86400 (seconds). No special formatting needed.
• Add times without rollover: sum cells with =SUM() and apply the custom format [h]:mm to the total cell.
• Handle overnight shifts: include dates with the times, or use the =MOD(end-start,1) formula and format as [h]:mm.

After you apply the right formula and format, Excel shows exactly what you expect—8 hours, 25 hours, or the difference down to the second. The examples above cover the most common scenarios you’ll run into.