x
Solved

# Age Calculation including Leap Year

Posted on 2008-10-08
Medium Priority
940 Views
Hi Experts

I need to calculate the difference in days between the fields below. This needs to include a extra day if there is Leap Year.

Start Date       End Date
12/01/2007     10/31/2008 = 0.9171 (No leap year considered)

Thanks.
0
Question by:Sariff
• 6
• 4
• 3

Author Comment

ID: 22671548
Start Date       End Date
12/01/2007     10/31/2008 /365.25 = 0.9171 (No leap year considered)

0

LVL 75

Expert Comment

ID: 22671558
0

LVL 53

Expert Comment

ID: 22672232
> .. the difference in days between the fields ..

intDiffDays = DateDiff("d", [Start Date], [End Date])

leap year or not.

> 0.9171

That looks more like a partial year.
So what are you looking for?

/gustav

0

LVL 75

Expert Comment

ID: 22672303
I forgot to mention to look for Gustav's solutions in the links I posted :-)

mx
0

Author Comment

ID: 22672827
Im checking into them...I will post back soon. Thanks
0

Author Comment

ID: 22675669
The "days" difference for the example above is 335 (non leap year), 336 I need the formula to calc correctly for a Leap Year as well as non leap year.
Below is the calc I am using:
DateDiff("d",[LastBirthday],[LASTDAY_OF_MONTH]+1)/365

How can this be setup to know when it is a leap year and when not?
0

LVL 75

Expert Comment

ID: 22675727
gustav is your guy for any date / time type calculation.  In the one link, is Function covers every case known to Man.

mx
0

LVL 53

Expert Comment

ID: 22676156

But there is more. How about this, the 3-3-System (r), which may be new to you, invented by a Swede, Sune Nygren:

http://www.tretre.se/korteng.shtml

Notice the calendars which are "imune" to leap years:

http://www.tretre.se/standard.shtml

By the way, it was me who persuaded him to define a start date which he set to 1998-04-06.

/gustav
0

Author Comment

ID: 22677359
Thanks for the information. I am hoping for something I can run in SQL as the result will dump to an Excel doc...
0

LVL 53

Expert Comment

ID: 22678224
If it is in Access, it can be "dumped" to Excel, SQL or not.

Isn't it about time you try to tell us what you are trying to achieve?

/gustav
0

Author Comment

ID: 22678395
gustav

I am trying to calculate the Age of an employee to the exact day in years and the lapsed days since his/her last birthday.

0

Author Comment

ID: 22678448
I have the Age in years done, but the Leap year causes a problem with calculating the lapsed days
0

LVL 53

Accepted Solution

Gustav Brock earned 2000 total points
ID: 22679303
That could be counting the days from the last birthday. Leap years will have no impact as a day is a day. Thus, for some years you may get x years and 365 days just before the birthday:

datDob = <some birthday>
intAgeYears = Age(datDob)
intAgeDays = DateDiff("d", DateAdd("yyyy", intAgeYears, datDob), Date)

strAgeYearsDays = CStr(intAgeYears) & " years, " & CStr(intAgeDays) & " days"

/gustav
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.