Table of Contents
Calculating the difference between two datetimes in Excel is a simple process that can be done using the built-in functions and formulas. First, you need to have two datetime values in your worksheet, which can be entered manually or imported from an external source. Then, using the DATEDIF function, you can easily calculate the difference between the two datetimes in various units such as days, months, or years. Alternatively, you can also use the subtraction operator (-) to directly subtract one datetime from the other and get the difference in days, which can then be converted to other units using further calculations. By following these steps, you can accurately determine the time duration between two datetimes in Excel, making it a useful tool for managing and analyzing time-related data.
Excel: Calculate Difference Between Two Datetimes
Often you may want to calculate the difference between two datetimes in Excel.
Fortunately this is easy to do and the following example shows how to do so.
Example: How to Calculate Difference Between Two Datetimes in Excel
Suppose we have the following dataset in Excel that shows the starting datetime and ending datetime that various athletes completed some task:
Suppose we would like to create a new column that calculates the difference between each starting and ending datetime.
To do so, we can type the following formula into cell D2:
=C2-B2
We can then click and drag this formula down to each remaining cell in column D:
By default, Excel displays the difference between the datetimes using a number format.
To change this format, highlight the cell range D2:D10, then type Ctrl + 1 to bring up the Format Cells window.
Then click Custom from the Category menu, then type in d h:mm:ss into the Type box:
Once you click OK, each of the numbers in column D will be formatted using days, hours, minutes and seconds:
For example:
- The difference between the starting datetime and ending datetime for Andy is 0 days, 17 hours and 24 minutes.
- The difference between the starting datetime and ending datetime for Bob is 1 day, 18 hours and 23 minutes.
- The difference between the starting datetime and ending datetime for Chad is 0 days, 0 hours and 9 minutes.
And so on.
Note: If the difference between each datetime is less than 24 hours, you could instead use the formula h:mm:ss in the Type box of the Format Cells window since it’s assumed that the value for days is equal to zero.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel: