How to Do Time Calculations in Excel: Formulas, Formats, and Common Fixes

Time calculation is one of those Excel tasks that looks simple until it isn't. Hours worked, elapsed time, shift durations, countdown timers — Excel can handle all of it, but only once you understand how it actually stores time under the hood. Get that part wrong, and your results will be off in ways that are quietly confusing.

How Excel Stores Time (This Is the Key)

Excel doesn't store time as a clock value. It stores it as a decimal fraction of a 24-hour day. So:

• 0.5 = 12:00 PM (half a day)
• 0.25 = 6:00 AM (a quarter of a day)
• 0.75 = 6:00 PM (three-quarters of a day)

A full datetime value combines both: the integer portion represents the date, and the decimal represents the time. For example, 45123.5 means a specific date at noon.

This decimal system is why time math in Excel usually works with simple arithmetic — but also why results can look broken when the format isn't set correctly.

Basic Time Subtraction: Calculating Elapsed Time ⏱️

To find how much time passed between two points:

=B2 - A2 

Where A2 is the start time and B2 is the end time. If both cells are formatted as Time, the result cell also needs to be formatted as Time — otherwise you'll see a decimal like 0.375 instead of 9:00 AM.

Critical rule: If the end time is earlier than the start time (crossing midnight), simple subtraction produces a negative number. Excel displays this as ####### or a string of hashes. The fix:

=IF(B2 < A2, B2 + 1 - A2, B2 - A2) 

Adding 1 accounts for the rollover to the next day.

Adding and Subtracting Hours, Minutes, or Seconds

Because time is a fraction of a day, you need to convert your units before adding them:

Alternatively, use the TIME function:

=A2 + TIME(3, 30, 0) 

This adds 3 hours and 30 minutes cleanly, without needing to remember the divisors. TIME(hours, minutes, seconds) always returns a value between 0 and 1, so it's best for adding increments, not for representing totals greater than 24 hours.

Calculating Total Hours Worked

This is where most people run into trouble. If you subtract a start time from an end time and format the result as h:mm, Excel caps the display at 23:59. To display totals beyond 24 hours, use square brackets in the format:

1. Select the result cell
2. Open Format Cells → Custom
3. Enter: [h]:mm

The square brackets around h tell Excel to show the true cumulative hours rather than wrapping back to zero at 24.

For a full weekly timesheet, summing a column of durations formatted with [h]:mm will correctly show totals like 42:30 for 42.5 hours.

Extracting Hours, Minutes, and Seconds Separately

When you need individual components from a time value, use these functions:

• =HOUR(A2) — returns the hour (0–23)
• =MINUTE(A2) — returns the minutes (0–59)
• =SECOND(A2) — returns the seconds (0–59)

These are read-only extractions — they pull the value but don't affect the stored time.

Converting Time to Decimal Hours (for Payroll and Reports)

Many payroll systems and reports want hours as a decimal number — 8.5 rather than 8:30. The conversion is straightforward:

=A2 * 24 

Multiply the time value by 24 to get decimal hours. Format the result as a Number, not as Time, or the output will still display as a time format.

To go the other direction — converting decimal hours back to a time value:

=A2 / 24 

Then format the result as [h]:mm.

Using NOW() and TODAY() in Time Calculations 🕐

=NOW() returns the current date and time. =TODAY() returns just the current date (with no time component).

To calculate how much time has elapsed since a recorded timestamp:

=NOW() - A2 

Format the result as [h]:mm for hours and minutes. Keep in mind that NOW() is volatile — it recalculates every time the workbook recalculates, so the elapsed time updates automatically, which is useful for live dashboards but can be distracting in static reports.

Common Formatting Pitfalls

Bold the following distinctions — they trip people up constantly:

• A cell formatted as General showing a time value will display as a decimal. Change the format to Time or a custom h:mm format.
• Summing times that exceed 24 hours requires [h]:mm, not h:mm.
• Copying time values as plain numbers (Paste Special → Values) strips the format — the value stays correct, but the format needs to be reapplied.
• Text that looks like a time ("9:00 AM" entered as text) will not calculate. Check for left-aligned values in time cells — time values are right-aligned by default.

Variables That Affect Your Approach

How you structure time calculations depends heavily on your specific use case:

• Shift schedules crossing midnight require the overflow correction formula, while daytime-only calculations don't
• Payroll processing typically needs decimal output and may require rounding to nearest quarter-hour increments using MROUND
• Project time tracking with long durations benefits from storing values as decimal hours from the start rather than converting later
• Users on older Excel versions (pre-2016) may find that some IFS or TEXTJOIN workarounds for complex schedules aren't available
• Excel on Mac vs. Windows uses a different date serial number baseline by default (1904 vs. 1900), which can cause errors when sharing files across platforms

The right formula structure for one person's weekly timesheet may not suit someone building a live operations dashboard or processing exported data from a time-tracking app. The arithmetic is consistent — but the formatting, overflow handling, and output format shift depending on what the numbers need to do next.