No. of years btw 2 dates

[hsandeep]

2 dates are there in 2 cells in DD-MMM-YYYY format.
How to get no. of years in between the 2 dates?
Assume:

1. both dates are to be included AND
2. for 183 days and above, year to be rounded up AND
3. for <183 days and below, year to be rounded down.

Thanx

 

[Soggy]

This could possbily do it where first date is in A2 and second is on B2:

IF(DATE(YEAR(B2),MONTH(B2),DAY(B2))-DATE(YEAR(B2),MONTH(A2),DAY(A2))>183,YEAR(B2)-YEAR(A2)+1,IF(DATE(YEAR(B2),MONTH(B2),DAY(B2))-DATE(YEAR(B2),MONTH(A2),DAY(A2))<-183,YEAR(B2)-YEAR(A2)-1,YEAR(B2)-YEAR(A2)))

You can adapt it so it will work out based on actual months rather than days then it can incorporate leap years.

 

[Jonmo1]

Perhaps

=ROUND((B1-A1)/365.25,0)

A1 = Start Date
B1 = End Date

 

[andrewkard]

Hello,

<CITE>=DATEDIF(Date1, Date2, “y”)</CITE>
​

 

[hsandeep]

ADVERTISEMENT

Thanks Soggy,
IF(DATE(YEAR(B2),MONTH(B2),DAY(B2))-DATE(YEAR(B2),MONTH(A2),DAY(A2))>183,YEAR(B2)-YEAR(A2)+1,IF(DATE(YEAR(B2),MONTH(B2),DAY(B2))-DATE(YEAR(B2),MONTH(A2),DAY(A2))<-183,YEAR(B2)-YEAR(A2)-1,YEAR(B2)-YEAR(A2)))

Now i am using your above formula. pls tell:
1. Is both days INCLUDED while arriving at no. of years?
2. Is formula taking care of leap years?

 

[hsandeep]

Perhaps

=ROUND((B1-A1)/365.25,0)

A1 = Start Date
B1 = End Date

Sir, Can u explain 365.25? Is both dates INCLUDED?

 

[Jonmo1]

ADVERTISEMENT

There are 365 days in a NON leap year
366 in a leap year (every 4 years)
The .25 accounts for that

No, it didn't include both dates...Just add 1 to the B1-A1
=ROUND((B1-A1+1)/365.25,0)

 

[Soggy]

What my formula does is work out the diffrenece in years so 2012 to 2015 will be 3 years difference. It them calculates the diffrence it the dates ere from the same year. If the difference is greater than 183 then add a year if it is less than -183 take a year off. So will take the 2 categories into account. You can refine it to work out if whether it's 6calendar months difference rather than the 183 days. Thsi will just compensate for the leap year where there is 366 days rather than 365 but coniderign your looking at years difference this shouldn't be a year.

Jonmo's clever way is by taking the 365.25 which is the actual amount of days (the leap day in a year is just those 4 quarter days combined) in a year and dividing by 4. Could be the better option than mine.

 

[hsandeep]

There are 365 days in a NON leap year
366 in a leap year (every 4 years)
The .25 accounts for that

No, it didn't include both dates...Just add 1 to the B1-A1
=ROUND((B1-A1+1)/365.25,0)

Suppose leap year does not fall btw 2 dates, will your formula work accurately?

 

[Jonmo1]

2011 is a non leap year..

Excel Workbook
	A	B	C	D
1	1-Jan-11	29-Jun-11	180	0
2		30-Jun-11	181	0
3		1-Jul-11	182	0
4		2-Jul-11	183	1
5		3-Jul-11	184	1
6		4-Jul-11	185	1
Sheet1