Eligible date

[T]

I need to do a calculation when an employee is eligible for a certain
benefit. The eligibility is basically the first day of the first
calendar
quarter (4/1) or 2nd eligible (10/1) AFTER one complete year of
employment. For example, if a person's
hire date is 9/5/2003, he becomes eligible on 10/1/2004, which is one
year
(9/5/2003 to 9/5/2004), plus the first day of the next quarter, which
is
10/1/2004. If his hire date is 10/2/2004, he won't be eligible until
4/1/2006, which is one year (10/2/2004 to 10/1/2005), plus the start of
the
next quarter which is April of 2006. Does anyone have any idea how I
can
accomplish this using [HireDate] as the starting point? I hope this is
clear!

 

[T]

Thank you Mr B. I'll give it a try and let you know.

T,

Here is a function that should return the calculation you are looking for.

'Start of code:

Function EligibleDate(HireDate As Date) As Date
Dim lngHireMo As Long
Dim lngQtrMo As Long
Dim lngEligibleYear As Long

lngHireMo = Month(HireDate)
Select Case lngHireMo
Case 1 To 3
lngQtrMo = 4
lngEligibleYear = Year(HireDate) + 1
Case 4 To 6
lngQtrMo = 7
lngEligibleYear = Year(HireDate) + 1
Case 7 To 9
lngQtrMo = 10
lngEligibleYear = Year(HireDate) + 2
Case 9 To 12
lngQtrMo = 1
lngEligibleYear = Year(HireDate) + 1
End Select
EligibleDate = DateSerial(lngEligibleYear, lngQtrMo, 1)
End Function
'end of code

To call this code, use this statement:
Me.txtElibibleDate = EligibleDate(Me.txtHIreDate)

This assumes that "txtElibibleDate" is the name of a text box where you want
the calculation to be displayed on your form and the "txtHireDate" is the
name of the text box where you have entered the Hire Date.

I think that you may have inadvertently stated the last senerio incorrectly.
If you did not then the Function above will return the correct calculation
for your first senerio and the wrong calculation for your second senerio. I
really think the in the second senerio the eligible date will actually be
1/1/2006. The next quarter folling his full year of employment would be
January 1st.

If I am not correct, I stand corrected.

--
HTH

Mr B

I need to do a calculation when an employee is eligible for a certain
benefit. The eligibility is basically the first day of the first
calendar
quarter (4/1) or 2nd eligible (10/1) AFTER one complete year of
employment. For example, if a person's
hire date is 9/5/2003, he becomes eligible on 10/1/2004, which is one
year
(9/5/2003 to 9/5/2004), plus the first day of the next quarter, which
is
10/1/2004. If his hire date is 10/2/2004, he won't be eligible until
4/1/2006, which is one year (10/2/2004 to 10/1/2005), plus the start of
the
next quarter which is April of 2006. Does anyone have any idea how I
can
accomplish this using [HireDate] as the starting point? I hope this is
clear!

 

[T]

I tweeked it a little and it works. Thanks
Now I need to check to see if the hire date is less than or greater
than the check date. If my check date is in 2000 and my hire date is
in 1998 it doesn't calculate the benefit (which is wrong). If my check
date is in 2000 and my hire date is in 2000 it calculates (which is
correct). I need a way to calculate the difference between the hire
date and check date in julian dates. Any suggestions?

T,

Here is a function that should return the calculation you are looking for.

'Start of code:

Function EligibleDate(HireDate As Date) As Date
Dim lngHireMo As Long
Dim lngQtrMo As Long
Dim lngEligibleYear As Long

lngHireMo = Month(HireDate)
Select Case lngHireMo
Case 1 To 3
lngQtrMo = 4
lngEligibleYear = Year(HireDate) + 1
Case 4 To 6
lngQtrMo = 7
lngEligibleYear = Year(HireDate) + 1
Case 7 To 9
lngQtrMo = 10
lngEligibleYear = Year(HireDate) + 2
Case 9 To 12
lngQtrMo = 1
lngEligibleYear = Year(HireDate) + 1
End Select
EligibleDate = DateSerial(lngEligibleYear, lngQtrMo, 1)
End Function
'end of code

To call this code, use this statement:
Me.txtElibibleDate = EligibleDate(Me.txtHIreDate)

This assumes that "txtElibibleDate" is the name of a text box where you want
the calculation to be displayed on your form and the "txtHireDate" is the
name of the text box where you have entered the Hire Date.

I think that you may have inadvertently stated the last senerio incorrectly.
If you did not then the Function above will return the correct calculation
for your first senerio and the wrong calculation for your second senerio. I
really think the in the second senerio the eligible date will actually be
1/1/2006. The next quarter folling his full year of employment would be
January 1st.

If I am not correct, I stand corrected.

--
HTH

Mr B

I need to do a calculation when an employee is eligible for a certain
benefit. The eligibility is basically the first day of the first
calendar
quarter (4/1) or 2nd eligible (10/1) AFTER one complete year of
employment. For example, if a person's
hire date is 9/5/2003, he becomes eligible on 10/1/2004, which is one
year
(9/5/2003 to 9/5/2004), plus the first day of the next quarter, which
is
10/1/2004. If his hire date is 10/2/2004, he won't be eligible until
4/1/2006, which is one year (10/2/2004 to 10/1/2005), plus the start of
the
next quarter which is April of 2006. Does anyone have any idea how I
can
accomplish this using [HireDate] as the starting point? I hope this is
clear!

 

[John Vinson]

I need a way to calculate the difference between the hire
date and check date in julian dates.

What do you mean by "in julian dates"? A Julian Date means many things
(astronomical JD, Modified Astronomical JD, military JD, ....) - but
all that I know of refer to ways of specifying A DATE. A difference
between two dates isn't a date - it's a number (of days, years, or
some other unit of time).

Please give an example of how (datatype and appearance) your hire date
and check date are stored, and how you would like the result displayed
for those dates.

John W. Vinson[MVP]

 

[T]

Hi John,
I thought I would use the Julian date because I wasn't able to check
if the eligible date was => than check date. I have since found that
CDate will compare date to date.
Thank you for your response.

 

[John Vinson]

Hi John,
I thought I would use the Julian date because I wasn't able to check
if the eligible date was => than check date. I have since found that
CDate will compare date to date.

DateDiff() may be a better choice, actually; CDate() converts a text
string to a date/time value, which can be useful but isn't (directly)
a way to compare dates.

If you have two date/time values, you can simply compare them
numerically to see which is earlier.

John W. Vinson[MVP]