Excel Calculate Hours Minutes Seconds Between Two Times

This guide explains how to calculate the hours, minutes, and seconds between two times in Excel. You enter a start time and an end time, and Excel returns the difference formatted as a duration (e.g., 8:30:15) or as a decimal number of hours. The key is understanding how Excel stores times as fractions of a day and using the right subtraction formula and number format.

Formula

The core formula is simple: End time – Start time. In Excel, both times are stored as decimal fractions of a 24-hour day. For example, 6:00 AM is 0.25, and 6:00 PM is 0.75. Subtracting the start from the end gives the elapsed time as a fraction of a day. To display that fraction as hours, minutes, and seconds, you format the cell with a custom time format like [h]:mm:ss. The square brackets around the h allow the hours to exceed 24 (e.g., 30:15:00).

If you need the result in decimal hours (e.g., 8.5 for 8 hours 30 minutes), multiply the difference by 24. To get decimal minutes, multiply by 1440 (24 × 60). The formula in the calculator block uses the same logic: (u - t) * 1440 converts the day fraction to minutes, which is then displayed as a duration.

Worked Example

Using the default values from the calculator: Start time = 09:00:00, End time = 17:30:45.

1. Convert both times to day fractions: 09:00:00 = 9/24 = 0.375; 17:30:45 = 17.5125/24 ≈ 0.7296875.
2. Subtract: 0.7296875 – 0.375 = 0.3546875 (this is the fraction of a day).
3. Multiply by 1440 to get minutes: 0.3546875 × 1440 = 510.75 minutes.
4. Display as duration: 510.75 minutes = 8 hours, 30 minutes, 45 seconds (since 0.75 minutes = 45 seconds).

The calculator returns 8:30:45, matching the manual calculation.

How Excel Stores Dates and Times

Excel treats a date as a whole number (the number of days since January 0, 1900) and a time as a fractional part of that number. For instance, the serial number 45000.5 represents noon on a specific date. When you subtract two date-time values, you get a decimal that represents the elapsed days and fraction of a day. This is why you can simply subtract cells containing times—no special function is required. However, if you only enter times (without dates), Excel assumes they are on the same day. If the end time is earlier than the start time (e.g., a night shift from 22:00 to 06:00), a simple subtraction returns a negative value. To handle overnight shifts, you can use =MOD(end - start, 1) or add 1 to the end time if start > end.

Choosing the Right Number Format

The format you apply to the result cell determines how it displays. Here are common options:

• [h]:mm:ss – shows total hours (can exceed 24), minutes, and seconds. Best for durations.
• h:mm:ss – shows hours from 0 to 23, resetting after 24. Not ideal for durations over 24 hours.
• hh:mm:ss AM/PM – shows a clock time, not a duration. Avoid for elapsed time.
• 0.00 – shows decimal hours (e.g., 8.50 for 8 hours 30 minutes).

To apply a custom format, right-click the cell, choose Format Cells, go to the Number tab, select Custom, and type the format code. For decimal hours, you can also simply multiply the difference by 24 and use a number format.

Common Mistakes and How to Avoid Them

One frequent error is using a time format that resets at 24 hours, making a 30-hour shift appear as 6:00:00. Always use square brackets [h] for total hours. Another mistake is forgetting that Excel stores times as fractions—entering 9:00 as text will not work. Ensure your cells are formatted as Time (or General) before typing. Also, if you subtract two cells that contain both date and time, the result includes days; you may need to convert to hours by multiplying by 24. Finally, when using the TEXT function to extract parts (e.g., =TEXT(B2-A2,"h")), remember that it returns text, not a number, which can break further calculations.

Handling Overnight and Multi-Day Intervals

For intervals that span midnight, the simple subtraction fails. The robust formula is =MOD(end - start, 1), which returns the positive remainder after dividing by 1 (the day). For example, start 22:00, end 06:00: MOD(0.25 - 0.9167, 1) = 0.3333, or 8 hours. For intervals longer than 24 hours, include the date in your cells (e.g., 2025-01-01 09:00) and subtract normally; the result will include days. To display total hours, format as [h]:mm:ss—the square brackets allow hours beyond 24.

Alternatives: DATEDIF and TEXT Functions

While simple subtraction is best, Excel offers functions for specific needs. DATEDIF calculates the difference in complete days, months, or years, but it does not handle hours and minutes. For extracting components, you can use HOUR, MINUTE, and SECOND functions: =HOUR(B2-A2) returns the hour component (0-23), not the total hours. If you need a text string like “8 hours, 30 minutes”, use =TEXT(B2-A2,"h")&" hours, "&TEXT(B2-A2,"m")&" minutes". However, these are rarely necessary; the custom format approach is simpler and keeps the result numeric.

Frequently Asked Questions

Why does my time difference show as a decimal like 0.354?

Because Excel stores times as fractions of a day. To see hours, multiply by 24 and format as a number, or apply a time format like [h]:mm:ss to the cell.

How do I calculate the difference when the end time is before the start time (overnight)?

Use the MOD function: =MOD(end - start, 1). This adds 1 day if the result is negative, giving the correct positive duration.

Can I get the result in total minutes instead of hours and minutes?

Yes. Multiply the time difference by 1440 (the number of minutes in a day). For example, =(B2-A2)*1440 gives total minutes as a decimal.

Why does my duration show 0:00:00 after formatting?

This usually happens when the cell contains text, not a real time. Make sure you enter times with a colon (e.g., 9:00) and that the cell is formatted as Time or General before typing.

How do I sum many time durations and get a total over 24 hours?

Use the SUM function on the duration cells, then apply the custom format [h]:mm:ss to the result cell. The square brackets allow the hour count to exceed 24.

Does subtracting dates and times together work the same way?

Yes. If your cells contain both a date and a time (e.g., 2025-01-01 9:00), subtraction gives the total elapsed days and fraction. To get hours, multiply by 24 and format as a number.

Related

• 1440p 210Hz vs 1080p 240Hz: Which Monitor to Pick
• Alternatives to Gravity Forms: Top 5 Form Builders
• OLED Gaming Laptop Burn-In, Brightness & Everyday Use

in t | Start time (hh:mm:ss) | - | 09:00:00 | 0
in u | End time (hh:mm:ss) | - | 17:30:45 | 0
out dur | Duration (h:mm:ss) | - | (u - t) * 1440 | time
out dec | Decimal hours | h | (u - t) * 24 | 2