Calculating the elapsed time is something we all do daily. We’ve seen elapsed time calculations in sports, competitions, tasks, travel, etc.
Since Excel stores time values as numbers in the backend, it allows us to use simple formulas to calculate the elapsed time when we have the start and the end date/time values.
In this article, I’ll show you how to calculate the elapsed time in Excel using different methods.
Method 1 – Using a Simple Subtraction to Calculate Elapsed Time in Excel
In Excel, we can calculate the elapsed time by subtracting the start time from the end time.
The table below illustrates how long it took three candidates to answer a question. Column A contains the names, column B contains the start time, and column C contains the end time. In column D, I want to calculate the time taken by each candidate.
To find the difference between start time and end time, we can use the below formula in cell D2.
=C2-B2
But the results in column D are not in the correct format. We need results in hours and minutes.
We can change the format of column D by following the instructions below.
- Select calculated cells in column D. So, in this case, we have to select cells D2 to D4.
- Then, press “Control + 1” to open the Format Cells dialog box and go to the “Custom” category in the “Number” tab.
- Enter the below number format in the “Type:” box.
[hh]:mm
- Finally, click the “OK” button in the Format Cells dialog box.
Then we can see that column D is formatted as follows.
I have used the number formatting of “[hh]:mm” for the elapsed time calculation. If the start and end times are on the same date, you can even use, “hh:mm” as the formatting type. But, if the dates are falling on different dates, “hh:mm” will give you the wrong results. Because the “hh:mm” formatting ignores the date (where the hour value is more than 24 hours).
We can check the difference between the two number formats in the below example.
Work began at 10 am on April 25th, 2023, and it was completed at 2 pm the following day.
Even though the same formula is used in both cells C2 and C3, the formatting for the two cells is different.
We can see that the calculation in cell C3 is misleading. So, use [hh]:mm format.
Also read: Calculate Hours between two Times in Excel
Method 2 – Using the TEXT Function to Calculate Elapsed Time in Excel
We have to manually change the result cells’ number format when using the previous method.
If we want to get the time difference in the correct format when we get the result, we can use the Excel TEXT function.
With the help of this function, we can apply our chosen number format to the result.
The table below shows how long it took three applicants to solve a problem during a test. Column A contains the names, column B contains the start time, and column C contains the end time. In column D, we need to find the time spent by each applicant.
To find the difference between start time and end time, we can use the below function in cell D2.
=TEXT((C2-B2),"[hh]:mm")
The syntax of the TEXT function is TEXT(value, format_text). The first argument of this function is value.
In this case, the value we want is the difference between the start time and the end time.
So, we enter (C2-B2) as the value. For the next argument, we have to enter the format code that we need to apply for the given value. We want to apply the format code of [hh]:mm.
So, we enter that code for the second argument within quotes.
If you forget to enter the format code within quotes, you will get the “Excel ran out of resources…” error.
When you click “OK”, you will get 0 for the answer.
So, always remember to enter the format code within quotes.
The result of the TEXT function is stored as text. So, we cannot do any further calculations with the results of the TEXT function. For example, if we try to get the total elapsed time of three applicants in this example, the answer is zero.
Also read: Add Minutes to Time in Excel
Method 3 – Using the HOUR and MINUTE Functions to Calculate Elapsed Time in Excel
Sometimes we want to write the elapsed time with words such as xx hours and xx minutes.
In that situation, we can use Excel HOUR and MINUTE functions and do the calculation.
The below table shows the time taken by three students to solve a math problem.
Names are given in column A, start time is in column B, and End time is in column C. Now, we need to find the time taken by each applicant in column D.
To find the difference between start time and end time, we can use the below function in cell D2.
=HOUR(C2-B2)&" hours and "&MINUTE(C2-B2)&" minutes"
In this formula, first, we subtract the start time from the end time.
Then, we extract hours using the HOUR function and extract minutes using the MINUTE function.
After that, we combine the result of the HOUR function and MINUTE function and other words with an ampersand sign. We have to enter text entries within quotes.
This method can be used to calculate when the gap between start time and end time is less than 24 hours.
If the time gap is more than 24 hours, we have to combine Excel DAYS function as well.
In the below example, we need to find the time difference. The start time and the end time are not on the same date. So, we use the Excel DAYS function as well.
=DAYS(B2,A2)&" days and "&HOUR(B2-A2)&" hours and "&MINUTE(B2-A2)&" minutes"
We’ve gone over three different methods to calculate elapsed time in Excel.
If you want to apply the calculated elapsed times in further calculations, you have to apply method 1.
We can’t do any calculations using the results of the last two methods because they are saved as text values.
Other Excel articles you may also like:
