Calculations using dates

A

Ant

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

Brian Kastel

Use the DateDiff() and DateAdd() functions.

Years = DateDiff("yyyy", StartDate, Now)
Months = DateDiff("m", StartDate, Now) Mod Years

RetiresOn = DateAdd("yyyy", 65, DOB)

--
Brian Kastel


--Original Message----------------

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

Douglas J. Steele

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

Brian Kastel

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!

--
Brian Kastel


--Original Message----------------

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