Calculations using dates

  • Thread starter Thread starter Ant
  • Start date Start date
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.
 
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.
 
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.
 
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.
 
Back
Top