Number of Days between 2 dates

SusanB - Jul 2, 2010 at 06:57 AM
 puppy - Jan 28, 2015 at 09:44 AM

i am trying to calculate the number of days between 2 dates i,e

09/08/1965-05/02/2007= 41 years and 181 days which is 15146 days

When i put in Cell A1 09/08/1965

and in Cell A2 05/02/2007

Then the formula as A2-A1 the figure shows as 15155 instead of 15146

Please can you help to explain how to change this

Many Thanks


2 responses

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jul 2, 2010 at 11:22 AM
Duplicate ?
Netscur Posts 21 Registration date Thursday July 23, 2009 Status Member Last seen April 6, 2012
Jul 2, 2010 at 02:10 PM
Requests should specify the date format they are using. In the above example I believe they listed the format as: DD/MM/YYYY. So if I understand correctly, the example is subtracting August 9, 1965 from February 5th, 2007 getting a result of 15155 days.

All that taken into account, I think the reason you are seeing a delta in results is the fact the calendar contains Leap year. Excel compensates for a leap year (additional days) where as your calculation resulting in 15146 does not appear to.

Perhaps this piece of information will lead you or someone else in the right direction or provide you a better answer.

Additionally, if you want to view your result as "Years, months, and days", try this formula (where your first date is in A1, and your second date is in A2).

DAY(A2)<DAY(A1))),1,0)&" years, "&MONTH(A2)-MONTH(A1)+IF(AND(MONTH(A2)
>=DAY(A1)),12,IF(AND(MONTH(A2)>MONTH(A1),DAY(A2)<DAY(A1)),-1)))&" months,
"&A2-DATE(YEAR(A2),MONTH(A2)-IF(DAY(A2)<DAY(A1),1,0),DAY(A1))&" days"


88 days how to calculate months and days