In this article, we’ll discuss different methods to calculate elapsed time in Excel. In our daily calculations, finding out the time interval between two dates is a common scenario. To calculate the elapsed time, we can use functions, conditions, and even custom formatting in Excel. Let’s follow the complete guide to learn all of this.
How to Calculate Elapsed Time in Excel: 8 Easy Ways
1. Calculate Elapsed Time in Excel by Subtracting Two Date Values
To find the elapsed time in Excel, we just need to subtract two cells that contain the start and end date. In this example, we’re going to calculate the elapsed time of World War 2. Here, cells B3 and C3 contain the starting and ending date of the war with time. In cell C5, we subtracted cell B3 from cell C3.
The subtraction returned a number that is not meaningful. Actually, it represents the difference in serial numbers of these two dates. As we know, Excel stores date as a serial number that starts from 1 at the date 1/1/1900. So, this output number means there was a total of 2077.43 days elapsed in WW2.
Now, we can calculate, Years by dividing the number of days(output) by 365,
Months by dividing the number of days (output) by 30,
Weeks by dividing the number of days (output) by 7,
Hours by multiplying the number of days (output) with 24
Minutes by multiplying the number of days (output) with 24*60,
Seconds by multiplying the number of days (output) with 24*60*60.
Read More: Calculate Elapsed Time Between Two Dates in Excel
2. Estimating Elapsed Time in Excel Using the TODAY, NOW, NETWORKDAYS Functions
Using Excel’s built-in functions like the NOW, TODAY, NETWORKDAYS, etc. we can easily compute elapsed time in Excel. To illustrate this, we’re going to calculate how many years, months, days, hours, minutes, and seconds are left to start the next Summer Olympic 2024.
2.1 Use of the TODAY Function
The TODAY function in Excel returns the current date displayed on the worksheet. Here in cell C3, we stored the starting date of summer Olympic 2024. In cell C4, we used the following formula to find the number of days left to start Olympic 2024 from today’s date.
=(C3-TODAY())
We calculated the number of months and weeks by dividing the days’ number by 30 and 7 respectively in cells C5 and C6. To find out the no of years left, we used the YEAR function in cell D6. The formula is:
=(YEAR(C3)-YEAR(TODAY()))
2.2 Use of the NOW Function
The NOW function returns the current date and time displayed in the Excel worksheet. In this example, we used this function to calculate hours, minutes, and seconds left to begin the Summer Olympic 2024.
2.3 Use of the NETWOKDAYS Function
The NETWORKDAYS function calculates the number of working days of a given time interval assuming 5 working days in a week starting on Monday.
Similar Readings
- How to Calculate Total Hours in Excel
- How to Calculate Hours Worked Minus Lunch with Excel Formula
- How to Calculate Total Time in Excel
- How to Calculate Cycle Time in Excel
- How to Calculate the Duration of Time in Excel
- How to Calculate Lag Time in Excel
- How to Calculate Lead Time in Excel
- How to Calculate Median Follow-up Time in Excel
3. Compute Elapsed Time Using the TEXT Function in Excel
The TEXT function in Excel converts a numeric value to text and displays it in a specific predefined format. The function takes two arguments: value and format_text.
In this example, we put the difference between the ending and starting dates of WW2 as the value argument and different formats to display the duration of the war in years, months, days, hours, minutes, and seconds. Here, cells C5 and B5 contain the ending and starting dates respectively.
Read More: How to Calculate Slack Time in Excel
4. Use of Custom Formatting to Calculate Elapsed Time in Excel
Excel also gives us the option to use different number formats to control how to display a value in a cell. Let’s follow the example to grab the idea.
In cell C7, we calculated the difference between two dates in cells C5 and B5, which are the ending and starting dates of WW2. The output is the serial number difference of the input dates.
To show the year difference of these two dates, follow the steps below:
Steps:
- Select cell C7 and press Ctrl + 1 to open the Format Cells
- Go to the Number
- Select the Custom
- Type yy in the input box.
- Hit OK.
- The output is the number of years elapsed in world war 2.
Similarly, we can calculate the other values using the different format codes shown in the below screenshot.
Read More: How to Calculate Percentage of Time in Excel
5. Handle Negative Values in Elapsed Time
If the end time is less than the start time, we cannot subtract normally to get the time difference. To handle this negative time, we can follow two ways.
5.1 Use of Conditional IF Statement
Apply the following formula in cell D4, where C4 contains the end time and B4 contains the start time.
=IF(C4<B4,1+C4-B4,C4-B4)
In this formula, we used the conditional IF statement to set the logic of whether the end time is less than the start time. If the logic returns TRUE, then it’ll add 1 with the time difference of two times assuming the clock running for a full 24 hour period.
5.2 Use of the MOD Function
The use of the MOD function can easily handle the above situation. The function changes the negative number to a positive one as it works for both the time in the same day and the time that spans midnight. We just need to use 1 as the divisor (2nd argument).
Read More: How to Calculate Hours and Minutes for Payroll Excel
Things to Remember
- The NETWORKDAYS function excludes Saturday and Sunday from a week while calculating the workdays between two dates.
- If we want to add a custom holiday list to calculate the working days between two dates, we need to use the INTL function.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Conclusion
Now, we know how to calculate elapsed time in Excel using 8 easy examples. Hopefully, it would you to use these features more confidently. Any questions or suggestions don’t forget to put them in the comment box below.
Related Articles
- How to Calculate Hours and Minutes in Excel
- How to Calculate Hours from Date and Time in Excel
- How to Calculate Time Difference Between AM and PM in Excel
- How to Calculate Difference Between Two Dates and Times in Excel
- How to Calculate On Time Delivery Performance in Excel
- How to Calculate Production per Hour in Excel
- How to Calculate Total Hours Worked in a Week in Excel
- How to Calculate Turnaround Time in Excel Excluding Weekends
- Calculate Hours Between Two Dates and Times in Excel Excluding Weekends
- How to Calculate Years of Service in Excel
- How to Calculate Travel Time Between Two Cities in Excel
- How to Calculate Turnaround Time in Excel
- How to Calculate Hours Between Two Times in Excel
- Excel Calculate Hours between Two Times after Midnight
- How to Calculate Average Handling Time in Excel
- How to Calculate Average Response Time in Excel
- How to Calculate Average Turnaround Time in Excel
- How to Calculate Billable Hours in Excel
- Excel Formula for Overtime over 8 Hours
- How to Calculate Hours Worked and Overtime Using Excel Formula
- Excel Formula to Calculate Overtime and Double Time
- Excel Formula To Calculate Time Worked
- How to Sum Time in Excel
- [Fixed!] SUM Not Working with Time Values in Excel
- Man Hours Calculation in Excel
- How to Create an Injection Molding Cycle Time Calculator in Excel