Introduction
When managing work logs or event records in Excel, you often encounter situations where you need to accurately calculate “how much time has passed from one date and time to the next.”
For example, let’s look at the following data:
- Start Date/Time (Cell A2): 2025/2/26 16:19:11
- End Date/Time (Cell B2): 2025/2/26 16:20:12
If you want to display the difference between these two times—the elapsed time—in a pure “time” format like “0:01:01” (1 minute and 1 second), how should you do it?
In this article, I will explain the formula for correctly calculating elapsed time in Excel and how to set the display format to your desired “Time” style.
Basic Formula to Calculate Elapsed Time
Excel internally manages dates and times as numbers called “Serial Values.” One day is treated as “1,” and time is treated as the decimal portion (e.g., 12:00 PM is 0.5).
Thanks to this mechanism, calculating elapsed time is a simple subtraction, even if the time spans across different days.
Assuming the Start Time is in cell A2 and the End Time is in cell B2: Enter the following formula in the cell where you want to display the elapsed time (e.g., Cell C2).
=B2-A2
(Subtract the Start Time from the End Time)
Setting the Display Format to “Time”
Simply entering the formula above might result in a display like 1900/1/0 0:01:01 (including a date) or a decimal like 0.000706019... (the serial value), depending on the cell’s current formatting.
To display this in the desired “0:01:01” format, you need to configure the “Format Cells” settings correctly.
Setup Steps
- Select the cell with the formula (Cell C2).
- Press Ctrl + 1 (or right-click the cell and select “Format Cells”).
- The “Format Cells” dialog will open. Under the “Number” tab, select “Custom” from the “Category” list.
- In the “Type” input box, enter the following format code:
h:mm:ss - Click “OK”.
Now, the cell will display “0:01:01”.
[Advanced] Calculating Elapsed Time Exceeding 24 Hours
If there is a possibility that the elapsed time will exceed 24 hours (e.g., you want to display it as 25:30:15), I recommend setting the “Custom” format code as follows:
[h]:mm:ss
By enclosing the h in square brackets [ ], Excel will correctly sum up and display the hours even if they exceed 24, rather than resetting the clock.
Summary
Calculating elapsed time in Excel can be done with a simple subtraction: “End Time – Start Time.”
The key lies in the “Format Cells” setting after the calculation. By correctly setting custom format codes like h:mm:ss or [h]:mm:ss, you can display data exactly as needed for log analysis or time management.
Please make use of this technique for your daily data aggregation tasks.
