Excel: How to 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:

Excel difference between two datetimes

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.