Number of Days between 2 dates

[Closed]
Report
-
 puppy -
Hello,

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 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Duplicate ?
Posts
21
Registration date
Thursday July 23, 2009
Status
Member
Last seen
April 6, 2012

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).

=YEAR(A2)-YEAR(A1)-IF(OR(MONTH(A2)<MONTH(A1),AND(MONTH(A2)=MONTH(A1),
DAY(A2)<DAY(A1))),1,0)&" years, "&MONTH(A2)-MONTH(A1)+IF(AND(MONTH(A2)
<=MONTH(A1),DAY(A2)<DAY(A1)),11,IF(AND(MONTH(A2)<MONTH(A1),DAY(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"

-Netscur
Hello,

88 days how to calculate months and days