Date calculation

[Guest]

Hi,
We have new recruits that already have relevant experience elsewhere at the
time of hiring. We would like to take these into account at the time of
hiring and need to the following calculation. Hiredate minus experience years
gives Start of Experience (date). My formula is like this: =DateAdd("y",[Hire
Date],-[Experience Yrs]). Experience yrs is the total exp. yrs the person
enters with. Example: Effective date is 1st July 2006 and the exp. yrs is 17
yrs. The calculation gives me 14th June 2006 i.o. 1st July 1989. It treats
the experience yrs as days. What I am doing wrong here?
Lupe

 

[Guest]

I think I found the problem, I switched the fields and entered "yyyy":
=DateAdd("yyyy",-[Experience Yrs],[Effective Date]. If you have a better
solution I would like to hear it though. Thanks in advance.
Lupe

 

[Douglas J. Steele]

No, that's probably the best. Another possibility would be:

DateSerial(Year([EffectiveDate]) - [Experience Yrs], Month([EffectiveDate]),
Day([EffectiveDate])

but that requires more function calls.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I think I found the problem, I switched the fields and entered "yyyy":
=DateAdd("yyyy",-[Experience Yrs],[Effective Date]. If you have a better
solution I would like to hear it though. Thanks in advance.
Lupe

Hi,
We have new recruits that already have relevant experience elsewhere at
the
time of hiring. We would like to take these into account at the time of
hiring and need to the following calculation. Hiredate minus experience
years
gives Start of Experience (date). My formula is like this:
=DateAdd("y",[Hire
Date],-[Experience Yrs]). Experience yrs is the total exp. yrs the person
enters with. Example: Effective date is 1st July 2006 and the exp. yrs is
17
yrs. The calculation gives me 14th June 2006 i.o. 1st July 1989. It
treats
the experience yrs as days. What I am doing wrong here?
Lupe

 

[Guest]

Doug,
Thanks for your help. I will keep this formula for if I need it in the future.
Regards, Lupe

No, that's probably the best. Another possibility would be:

DateSerial(Year([EffectiveDate]) - [Experience Yrs], Month([EffectiveDate]),
Day([EffectiveDate])

but that requires more function calls.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I think I found the problem, I switched the fields and entered "yyyy":
=DateAdd("yyyy",-[Experience Yrs],[Effective Date]. If you have a better
solution I would like to hear it though. Thanks in advance.
Lupe

Hi,
We have new recruits that already have relevant experience elsewhere at
the
time of hiring. We would like to take these into account at the time of
hiring and need to the following calculation. Hiredate minus experience
years
gives Start of Experience (date). My formula is like this:
=DateAdd("y",[Hire
Date],-[Experience Yrs]). Experience yrs is the total exp. yrs the person
enters with. Example: Effective date is 1st July 2006 and the exp. yrs is
17
yrs. The calculation gives me 14th June 2006 i.o. 1st July 1989. It
treats
the experience yrs as days. What I am doing wrong here?
Lupe