How to Calculate the Time Difference Quickly Using Excel Functions

Date:2019-10-16 Author:Cordelia

In practice, we often need to calculate the difference of years, months, weeks or days between 2 separate dates. And like many other calculations, Excel functions can help you calculate the time difference quickly and accurately. Let’s see how to do it.

Case 1: Calculate the Difference of Years

Formula: =DATEDIF(A2,B2,”y”)

A2, B2 corresponds to the cells with the 2 different dates.

“y” refers to years.

1. Create a new column beside the two columns of dates and name it as you like.

How to Calculate the Time Difference Quickly Using Excel Functions

2. Enter the formula in the C2, click anywhere else or press [Enter] to work it out.

How to Calculate the Time Difference Quickly Using Excel Functions

3. Then use fill handle to apply the formula to other cells, thus you can calculate the time differences between the 2 columns in batch.

How to Calculate the Time Difference Quickly Using Excel Functions

Case 2: Calculate the Difference of Months

Formula: =DATEDIF(A2,B2,”m”)

A2, B2 corresponds to the cells with the 2 different dates.

“m” refers to months.

1. The process is nearly the same, just enter the right formula in C2.

How to Calculate the Time Difference Quickly Using Excel Functions

2. Use fill handle to apply this formula to other cells in column C. Thus you can calculate the differences of months between the entire column A and column B.

How to Calculate the Time Difference Quickly Using Excel Functions

Case 3: Calculate the Difference of Days

Formula: =DATEDIF(A2,B2,”d”)

A2, B2 corresponds to the cells with the 2 different dates.

“d” refers to days.

Enter the formula in C2 and use fill handle to apply it to multiple cells.

How to Calculate the Time Difference Quickly Using Excel Functions

Case 4: Calculate the Difference of Weeks

Formula: =DATEDIF(A2,B2,”d”)/7

A2, B2 corresponds to the cells with the 2 different dates.

“d” refers to days.

The difference of weeks is basically dividing the difference of days by 7.

1. Just enter the right formula in C2 and use fill handle to apply it to multiple cells.
2. And if the result shows like a date (like the picture below), it must due to the format of cells.

How to Calculate the Time Difference Quickly Using Excel Functions

3. You can right-click the selected cells and choose Format Cells in the menu. Then change the Category to Number, and customize the Decimal places as you like.

How to Calculate the Time Difference Quickly Using Excel Functions

4. Then the difference of weeks will be displayed correctly.

How to Calculate the Time Difference Quickly Using Excel Functions

Case 5: Calculate the difference of years, months and days separately

If you want to separately calculate the difference of numbers in years, months and days, you can use these formulas:

Years: =DATEDIF(A2,B2,”y”)

Months: =DATEDIF(A2,B2,”ym”)

Days: =DATEDIF(A2,B2,”md”)

Enter these formulas in 3 cells and press [Enter], you can get the result of the difference of years, months and days separately.

How to Calculate the Time Difference Quickly Using Excel Functions

Copyright Statement: Regarding all of the posts by this website, any copy or use shall get the written permission or authorization from Myofficetricks.

Leave a Reply

Your email address will not be published. Required fields are marked *