Date Differences in Years

[allenjc11]

Hello,

I have the following formula in Access 2007 to determine how many years
someone has been with the company.

-----

Years of Service: DateDiff("yyyy",[Hire Date],[TodaysDate])

-----

This formula is partially accurate, but not completely because, for example,
if someone was hired on 8/24/09 and today's date is 5/27/10, it says that
person has worked here for a year (the value result = 1). But because it
technically hasn't been a full year, the number isn't accurate. I would like
for it to return a value = 0 (zero) if it hasn't been a full year between the
two dates.

Is there something I can add to this formula to get an accurate year
difference?

Regards,
allenjc11

 

[Douglas J. Steele]

It's the same principle as calculating a person's age. Check
http://www.mvps.org/access/datetime/date0001.htm at "The Access Web".

 

[Dorian]

Look in Access HELP for full details on DateDiff statement.
How about figuring the difference in days and then looking for 365. Of couse
that will not take into account leap years.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[John W. Vinson]

Hello,

I have the following formula in Access 2007 to determine how many years
someone has been with the company.

-----

Years of Service: DateDiff("yyyy",[Hire Date],[TodaysDate])

-----

This formula is partially accurate, but not completely because, for example,
if someone was hired on 8/24/09 and today's date is 5/27/10, it says that
person has worked here for a year (the value result = 1). But because it
technically hasn't been a full year, the number isn't accurate. I would like
for it to return a value = 0 (zero) if it hasn't been a full year between the
two dates.

Is there something I can add to this formula to get an accurate year
difference?

One way:

Years of Service: DateDiff("yyyy",[Hire Date],[TodaysDate]) - IIF(Format([Hire
Date], "mmdd") > Format([TodaysDate], "mmdd"), 1, 0)

You can of course use the builtin Date() function in place of [TodaysDate] if
you really want the current date.