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

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

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
Co-author: Access 2010 Solutions, published by Wiley
(no e-mails, please!)

"allenjc11" <(E-Mail Removed)> wrote in message
news:B61935E5-B922-4552-B334-(E-Mail Removed)...
> 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

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


"allenjc11" wrote:

> 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

On Thu, 27 May 2010 12:53:29 -0700, allenjc11
<(E-Mail Removed)> wrote:

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

John W. Vinson [MVP]