Add years between two dates

[Robbin]

Hi,

I need to increase the date value of a field by roughly 1.29 years based on
a [dateofhire] field. My problem is that my resulting date MUST be 11/15 of
the second year after the date of hire.

Example: I'm hired on 07/01/2000 (the hire day and month will ALWAYS be
7/1) and I am eligible for a benefit on the 15th of November the SECOND year
after my hire date. I can add 502 days and that will get me pretty close,
but what happens on a leap year?

Right now, I'm using this: DateAdd("d",502,[DateofHire]) but will this work
in a leap year? I would rather have fixed dates and just increase the number
of years in between.

Thank you!

 

[Douglas J. Steele]

If what you need is November 15th, use

DateSerial(Year([DateofHire]) + 2, 11, 15)

 

[John Spencer]

Try using the DateSerial Function

DateSerial(Year(DateOfHire) +1 , 11, 15)


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Robbin]

You make it look soooo simple! Thank you!
--
Robbin

If what you need is November 15th, use

DateSerial(Year([DateofHire]) + 2, 11, 15)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi,

I need to increase the date value of a field by roughly 1.29 years based
on
a [dateofhire] field. My problem is that my resulting date MUST be 11/15
of
the second year after the date of hire.

Example: I'm hired on 07/01/2000 (the hire day and month will ALWAYS be
7/1) and I am eligible for a benefit on the 15th of November the SECOND
year
after my hire date. I can add 502 days and that will get me pretty close,
but what happens on a leap year?

Right now, I'm using this: DateAdd("d",502,[DateofHire]) but will this
work
in a leap year? I would rather have fixed dates and just increase the
number
of years in between.

Thank you!

 

[Robbin]

thank you so much!
--
Robbin

Try using the DateSerial Function

DateSerial(Year(DateOfHire) +1 , 11, 15)


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi,

I need to increase the date value of a field by roughly 1.29 years based
on
a [dateofhire] field. My problem is that my resulting date MUST be 11/15
of
the second year after the date of hire.

Example: I'm hired on 07/01/2000 (the hire day and month will ALWAYS be
7/1) and I am eligible for a benefit on the 15th of November the SECOND
year
after my hire date. I can add 502 days and that will get me pretty close,
but what happens on a leap year?

Right now, I'm using this: DateAdd("d",502,[DateofHire]) but will this
work
in a leap year? I would rather have fixed dates and just increase the
number
of years in between.

Thank you!