| | |
|
|
|
Calculations using dates |
|---|
| message from Ant on 27 May 2004 |
I am putting together a bit of a HR database but I am struggling with a
couple of date calculations.
The First
I would like a calculated field which returns the number of years and months
someone has been employed. I have a “Start_Date” field.
The second
I would like a projected retirement date calculated from a Date of birth
field “DOB” and given that the retirement age is 65.
Any help with either or both of the above would be much appreciated.
|
| Brian Kastel replied to Ant on 27 May 2004 |
Use the DateDiff() and DateAdd() functions.
Years = DateDiff("yyyy", StartDate, Now)
Months = DateDiff("m", StartDate, Now) Mod Years
RetiresOn = DateAdd("yyyy", 65, DOB)
|
| Douglas J. Steele replied to Brian Kastel on 27 May 2004 |
Years = DateDiff("yyyy", StartDate, Now)
isn't going to be totally accurate. If you go DateDiff("yyyy", #12/31/2003#,
#01/01/2004#), it will return 1 year, even though it's only 1 day between
the dates.
Years = DateDiff("m", StartDate, Date) \ 12 might be more appropriate than
using "yyyy"
As well, it should be
Months = DateDiff("m", StartDate, Now) Mod 12
not
Months = DateDiff("m", StartDate, Now) Mod Years
The code I have at
http://members.rogers.com/douglas.j.steele/Diff2Dates.html may be of use.
|
| Brian Kastel replied to Douglas J. Steele on 28 May 2004 |
You're absolutely right, and I thank you for the correction. If I'm going
to publish code, I should make sure I debug it well!
|
| Ant replied to Brian Kastel on 27 May 2004 |
Just the ticket Brian,
Thanks
Ant
"Brian Kastel" <be-ar-eye-ay-en-kay-ay-ess-tee-ee-ell@tampabay.rr.com> wrote
in message news:fKqtc.25816$0X2.847723@twister.tampabay.rr.com...
|
|
Archived message: Calculations using dates (MS Access Database)
