Calculating Days in a period

Closed
SusanB - Jul 2, 2010 at 06:40 AM
 Trowa - Jul 2, 2010 at 09:20 AM
Hello,


I have 2 dates that i want to work out the number of days between

i.e

09/08/1965-05/02/2007

This is 41 years and 181 days = total days is 15146

When i put in Cell A1 09/08/1965

and in Cell A2 05/02/2007

And do the formula as A2-A1 it comes out at 15155 instead of 15146???

How can i correct this so it does it automatically in excel?

Many Thanks for the help

Susan



1 response

Hi SusanB,

Aren't you forgetting about leap years?

You say it's 41 years and 181 days. If you don't consider leap years it's 41*365+181=15146. But a leap year has 366 days. Considering leap years will get you 15155 days.

Here is how you can determine if a year is a leaper or not:

1. If the year is evenly divisible by 4, go to step 2. Otherwise, go to step 5.
2. If the year is evenly divisible by 100, go to step 3. Otherwise, go to step 4.
3. If the year is evenly divisible by 400, go to step 4. Otherwise, go to step 5.
4. The year is a leap year (it has 366 days).
5. The year is not a leap year (it has 365 days).

Putting this info into a formula you will get:
=IF(OR(AND(REST(A1,4)=0,REST(A1,100)<>0),REST(A1,400)=0),"Leap Year","No Leap Year")
So put your yearnumber in A1 and the formula will tell you if it's a leap year or not.

I also want you to look at the following function: DATEDIF which can be very helpfull when determining days, months, and/or years between two dates.
This function is perfectly explained at the following location:
http://www.meadinkent.co.uk/xl_birthday.htm

Best regards,
Trowa