Elapsed Calendar Years

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I use a query, designed by someone else, that pulls the hire date for
employees from the database, and then performs a calculation on it to get the
number of years worked. It uses DateDiff to get the number of days between
now and the hire date, and then divides that value by 365.25.

Obviously, this is not good enough. Using this method, if it tries to
calculate the years elapsed between July 1, 1996 and July 1, 2005, it gives
this result:

8.9993155373032169746748802190281 years

But the result should be exactly 9 years.

I want it to calculate the number of CALENDAR years, not, y'know,
"astronomical years" (or whatever the term is for the time it takes the earth
to make one orbit around the sun). Calendar years are not all the same
length. This should be taken into account in the calculation.

Thus, the years from July 5, 2001 to June 3, 2004, should be 2 333/366,
which is in decimal:

2.9098360655737704918032786885246

But the years from July 5, 2000 to June 3, 2003 should be 2 332/365, which
is in decimal:

2.909589041095890410958904109589

I figure this kind of calculation is a bit too complex to all fit in the
field of the query. Probably it needs a macro. Does one like this perhaps
already exist? Or can someone point me in the right direction for writing it
myself?
 
Smcash,

Like this...

YearsWorked:
DateDiff("yyyy",[HireDate],Date())+(Format([HireDate],"mmdd")>Format(Date(),"mmdd"))
 
Thanks, but when I tried your formula, every result for "years worked" is an
integer. Which is not what I want.

smcash
 
Smcash,

My apologies. I did not read your original post carefully enough. I
thought I knew what you wanted.
 
Looked like a challenge, so I built this function last night. It seems to do
what you want. Just put it in a module and then call it in the query.
Doesn't seem appropriate for a macro.

Years of Service: ServiceYears([YourHireDateField])

....replacing "YourHireDateField" with the appropriate field name.
And, of course, the name "Years of Service" can be changed as you like to
fit your query/report.

There may be a way to reduce/simplify this function, but it seems to work as
intended. Note that it uses the days from the current date back one year as
the calculation denominator. And note that dates are inclusive, counting the
hire date and the current date.

In all my years calculating service time and other date ranges I've never
seen it done quite this way. Interesting. Always learning something new.

HTH Joe
--------------------
Public Function ServiceYears(HireIn As Date) As Double

If CStr(Format(HireIn, "mmdd")) <= CStr(Format(Date, "mmdd")) Then
ServiceYears = CInt(DateDiff("yyyy", HireIn, Date)) + _
(DateDiff("d", Format(HireIn, "mm/dd/" & (Year(Date))), Date) + 1) / _
DateDiff("d", DateSerial(Year(Date) - 1, Month(Date), Day(Date)),
Date)
Else
ServiceYears = CInt(DateDiff("yyyy", HireIn, Date) - 1) + _
(DateDiff("d", Format([HireIn], "mm/dd/" & Year(Date) - 1), Date) +
1) / _
DateDiff("d", DateSerial(Year(Date) - 1, Month(Date), Day(Date)),
Date)
End If

End Function
 
Oh, also...
Obviously, this is not good enough. Using this method, if it tries to
Click to expand...
calculate the years elapsed between July 1, 1996 and July 1, 2005, it gives
this result:

8.9993155373032169746748802190281 years

But the result should be exactly 9 years.<

The result, IMHO, should be 9.0027397260274. The hire date and the current
date should, I believe, be inclusive, with the 9th year ending on June 30,
2005. 9 years and 1 day calculated, with the decimal part being 1/365.

Joe

Is this the way you see it as well?
 
Well, no, that's not how I'd prefer it. The hire date should be included, but
not the current day. We don't want to count parts of days, only whole days,
so the day is not counted until it is over. If I'm running the calculation on
July 1, I'm actually not counting the day of July 1 in the calculation,
because July 1 is not over yet. But other than that, I think you've got the
right idea, thanks!
 
Oh, also: when I calculated 2 333/366 and 2 332/365 I'm not sure if I
included June 3 or not... I may have mistakenly included it. I don't
remember, sorry. But you get the idea.
 
If you don't want the current date counted, the function should look like this:
-----------------------------
Public Function ServiceYears(HireIn As Date) As Double

If CStr(Format(HireIn, "mmdd")) < CStr(Format(Date, "mmdd")) Then
ServiceYears = CInt(DateDiff("yyyy", HireIn, Date)) + _
(DateDiff("d", Format(HireIn, "mm/dd/" & (Year(Date))), Date)) / _
DateDiff("d", DateSerial(Year(Date) - 1, Month(Date), Day(Date)),
Date)
Else
ServiceYears = CInt(DateDiff("yyyy", HireIn, Date) - 1) + _
(DateDiff("d", Format([HireIn], "mm/dd/" & Year(Date) - 1), Date)) / _
DateDiff("d", DateSerial(Year(Date) - 1, Month(Date), Day(Date)),
Date)
End If

End Function
-----------------------

....and enter the field in the query like I indicated before. If you change
your mind about the days, just use the function as it was before.

Joe
 
Back
Top