No. of years btw 2 dates

hsandeep

Well-known Member
Joined
Dec 6, 2008
Messages
959
Office Version
  1. 2007
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Soggy

Board Regular
Joined
May 16, 2012
Messages
188
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Perhaps

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

A1 = Start Date
B1 = End Date
 

hsandeep

Well-known Member
Joined
Dec 6, 2008
Messages
959
Office Version
  1. 2007
Platform
  1. Windows

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?
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061

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

Board Regular
Joined
May 16, 2012
Messages
188
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

Well-known Member
Joined
Dec 6, 2008
Messages
959
Office Version
  1. 2007
Platform
  1. Windows
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,303
Messages
5,600,855
Members
414,407
Latest member
Zaner0445

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top