Calculating Number of Days, Weeks, Months and Years between Dates in Microsoft Excel

In this article you’ll learn, how to calculate number of days, weeks, months and years between 2 dates in Microsoft Excel.

To calculate the same, we’ll use INT, TODAY and MOD or we can use DATEDIF functions.

• INT function, will get the whole number without decimal
• MOD function, will divide the number by a divisor
• TODAY function, will help us get the current date
• DATEDIF function, will calculate difference between each pair of dates

Let’s take an example,

We have 2 dates,

• Cell A1 containing 1st date and
• Cell A2 containing 2nd date

To calculate the difference between years, use DATEDIF function as shown in the following formula:

• Select the cell A3 and write the formula =DATEDIF (A1,A2,”Y”)
• This function will return the value in years

                        

To calculate the difference in months, use the DATEDIF function as shown in the following formula:

• Select the cell A4 and write the formula =DATEDIF (A1,A2,”M”)
• This function will return the value in months

To calculate the difference between days, use the DATEDIF function as shown in the following formula:

• Select the cell A5 and write the formula =DATEDIF (A1,A2,”D”)
• This function will return the value in days

OR

Use the “YEAR”, “MONTH”, “AND” and “DAY” functions as shown in the following formula:-

• Select the cell A3 and write the formula to calculate the years
• =YEAR(A2)-YEAR(A1)-(MONTH(A2)/12)
• This function will return the no. of years in between 2 dates

Use the DATEDIF function to calculate the number of days over years:-

• Select the cell A4 and write the formula to calculate the years
• =DATEDIF(A1,A2,”y”)
• This function will return the no. of years in between 2 dates

PS: A lot of site, avoid calculating date in Excel using DATEDIF function. The reason is “bugs”. DATEDIF functions don’t have any documentation in Excel Help file.

But, Microsoft is continuously implying this feature / formula in all new version.

In case if you also want to avoid DATEDIF function, you can use manual calculation. Like below,

=INT((TODAY()-A1)/365.25) & ” years , ” & INT(MOD((TODAY()-A1)/365.25,1)*12) & ” months and ” & INT(MOD((TODAY()-A1)/30.4375,1)*30.4375) & ” days”.

It will give day difference in Year Month and in days. You can use A2 in case of today, where A2 is the greater day that A1 and gives you Elapsed time between these 2 dates.

Read more at exceltip.com:

http://www.exceltip.com/excel-date-time-formulas/calculating-number-of-days-weeks-months-and-years-between-dates.html