CODEWITHSUNDEEP
exceldateexcel-formula
Adam Morris
Adam Morris

Reputation: 8545

In Excel, how do I calculate days between two dates ignoring full years?

How do I calculate relative years + days between two dates in excel? I want to know the number of years and any additional days (outside of the full years). Dividing the total days by 365 doesn't work because of leap years. For example:

Cell A1 = '1/31/2015'
Cell B1 = '2/1/2025'
Cell C1 = '2/11/2025'
=(B1-A1)/365   # 10.0109 = 10 years + 4 days, looking for 10 years + 0 days
=(C1-A1)/365   # 10.0383 = 10 years + 14 days, looking for 10 years + 10 days

Is there an easy way to calculate this?

Note - this question is not a duplicate of How to find the difference between dates in VBA -- which is a question about calculating the difference between dates. This question is asking how to calculate the difference of both years and days, such that the difference in days is for the final year only, and are not incorrectly including leap year days for previous years.

Upvotes: 0

Views: 4594

Answers (2)

Michael Moulsdale
Michael Moulsdale

Reputation: 1488

You can use the documented function DATEDIF

=DATEDIF(startdate,enddate,"Y")

This will give you the difference in whole years

“Y” Returns the period difference as complete years.

“M” Returns the period difference as complete months.

“D” Returns the number of days in the period.

“MD” Returns the difference between the days in ‘Start_Date’ and ‘End_Date’. Here the months and years of the dates are ignored.

“YM” Returns the difference between the months in ‘Start_Date’ and ‘End_Date’. Here the days and years of the dates are ignored

“YD” Returns the difference between the days of ‘Start_Date’ and ‘End_Date’. Here the years of the dates are ignored.

Upvotes: 2

Isaac G Sivaa
Isaac G Sivaa

Reputation: 1299

Excel Solution :

=DATEDIF(A1,A2,"y") & " years, " & DATEDIF(A1,A2,"ym") & " months, " & DATEDIF(A1,A2,"md") & " days"

Please refer this link

Upvotes: 0

Related Questions