date formulas

[Guest]

Good Afternoon everyone-

I am a Human Resources Assistant creating a database for my director to keep
track of all Full Time Year Round employees.

I am having trouble doing date calculations. An employee needs to be active
for 90 days from the date they started Full Time Year Round (FTYR) and then
wait to the beginning of the next month in order to join the health program.
As an example, if an employee has a hire date of 01/15/06 their 90 days would
be 04/15/06. But they would have to wait until May 1 to join the health
insurance program. The formula I used in my form to figure 90 days looks
like this:
=DateAdd("d",90,Forms![Entry Edit Frm]!FTYRDate)

Does anyone know if I can add something to the formula to make it calculate
the 90 days plus going to the first of the next month? I also need to come
up with a similar formula for pension, which would be 1 year of service and
then either one of two open enrollment dates; May 1 or Nov 1 whichever is
closer.

Thank you in advance for your time and any help you can provide.

-A

 

[fredg]

Good Afternoon everyone-

I am a Human Resources Assistant creating a database for my director to keep
track of all Full Time Year Round employees.

I am having trouble doing date calculations. An employee needs to be active
for 90 days from the date they started Full Time Year Round (FTYR) and then
wait to the beginning of the next month in order to join the health program.
As an example, if an employee has a hire date of 01/15/06 their 90 days would
be 04/15/06. But they would have to wait until May 1 to join the health
insurance program. The formula I used in my form to figure 90 days looks
like this:
=DateAdd("d",90,Forms![Entry Edit Frm]!FTYRDate)

Does anyone know if I can add something to the formula to make it calculate
the 90 days plus going to the first of the next month? I also need to come
up with a similar formula for pension, which would be 1 year of service and
then either one of two open enrollment dates; May 1 or Nov 1 whichever is
closer.

Thank you in advance for your time and any help you can provide.

-A

I'm not sure what you actually wanted in the second part of the
question. The below query will return the first day of the following
month of the 90 day addition, as well as the following May or November
first, following the 1 year anniversary. Also I wasn't sure of what
to do if the yearly anniversary came out on May 1st. My code will set
the date to November 1st.
If that is not what you want, use similar logic to work it out.

Create a query:

SELECT DateAdd("d",90,[ADate]) AS Exp,
DateSerial(Year([Exp]),Month([Exp])+1,1) AS EffectiveDate,
DateAdd("yyyy",1,[ADate]) AS Exp2, IIf(Month([Exp2]) Between 5 And
10,DateSerial(Year([Exp2]),11,1),DateSerial(Year([Exp2]),5,1)) AS
YearEffectiveDate FROM YourTableName;

 

[fredg]

Good Afternoon everyone-

I am a Human Resources Assistant creating a database for my director to keep
track of all Full Time Year Round employees.

I am having trouble doing date calculations. An employee needs to be active
for 90 days from the date they started Full Time Year Round (FTYR) and then
wait to the beginning of the next month in order to join the health program.
As an example, if an employee has a hire date of 01/15/06 their 90 days would
be 04/15/06. But they would have to wait until May 1 to join the health
insurance program. The formula I used in my form to figure 90 days looks
like this:
=DateAdd("d",90,Forms![Entry Edit Frm]!FTYRDate)

Does anyone know if I can add something to the formula to make it calculate
the 90 days plus going to the first of the next month? I also need to come
up with a similar formula for pension, which would be 1 year of service and
then either one of two open enrollment dates; May 1 or Nov 1 whichever is
closer.

Thank you in advance for your time and any help you can provide.

-A

I'm not sure what you actually wanted in the second part of the
question. The below query will return the first day of the following
month of the 90 day addition, as well as the following May or November
first, following the 1 year anniversary. Also I wasn't sure of what
to do if the yearly anniversary came out on May 1st. My code will set
the date to November 1st.
If that is not what you want, use similar logic to work it out.

Create a query:

SELECT DateAdd("d",90,[ADate]) AS Exp,
DateSerial(Year([Exp]),Month([Exp])+1,1) AS EffectiveDate,
DateAdd("yyyy",1,[ADate]) AS Exp2, IIf(Month([Exp2]) Between 5 And
10,DateSerial(Year([Exp2]),11,1),DateSerial(Year([Exp2]),5,1)) AS
YearEffectiveDate FROM YourTableName;

 

[Guest]

Try this --
=DateAdd("m",2, DateAdd("d",90,Forms![Entry Edit
Frm]!FTYRDate)-Day(DateAdd("d",90,Forms![Entry Edit Frm]!FTYRDate)))

 

[Guest]

Try this --
=DateAdd("m",2, DateAdd("d",90,Forms![Entry Edit
Frm]!FTYRDate)-Day(DateAdd("d",90,Forms![Entry Edit Frm]!FTYRDate)))

 

[kerry_ja]

I would create a function something like:

Public Function FirstDayMonth90DaysLater(InititalDate As Date) As Date
Dim datPlus90 As Date
Dim intDay As Integer
Dim intMonth As Integer
Dim intYear As Integer

Let datPlus90 = DateAdd("d", 90, InititalDate)
Let intDay = Day(datPlus90)
Let intMonth = Month(datPlus90)
Let intYear = Year(datPlus90)

If intDay = 1 Then
FirstDayMonth90DaysLater = datPlus90
Exit Function
End If

Let intDay = 1
If intMonth = 12 Then
Let intMonth = 1
Let intYear = intYear + 1
Else
Let intMonth = intMonth + 1
End If

Let FirstDayMonth90DaysLater = DateSerial(intYear, intMonth,
intDay)
End Function

 

[kerry_ja]

I would create a function something like:

Public Function FirstDayMonth90DaysLater(InititalDate As Date) As Date
Dim datPlus90 As Date
Dim intDay As Integer
Dim intMonth As Integer
Dim intYear As Integer

Let datPlus90 = DateAdd("d", 90, InititalDate)
Let intDay = Day(datPlus90)
Let intMonth = Month(datPlus90)
Let intYear = Year(datPlus90)

If intDay = 1 Then
FirstDayMonth90DaysLater = datPlus90
Exit Function
End If

Let intDay = 1
If intMonth = 12 Then
Let intMonth = 1
Let intYear = intYear + 1
Else
Let intMonth = intMonth + 1
End If

Let FirstDayMonth90DaysLater = DateSerial(intYear, intMonth,
intDay)
End Function