Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

date formulas

Discussion in 'Information Technology' started by nhvwchic, Jul 28, 2006.

Tags:
  1. nhvwchic

    nhvwchic
    Expand Collapse
    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
     
  2. Loading...


  3. KARL DEWEY

    KARL DEWEY
    Expand Collapse
    Guest

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


    "nhvwchic" wrote:

    > 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
    >
     
  4. fredg

    fredg
    Expand Collapse
    Guest

    On Tue, 23 May 2006 10:22:01 -0700, nhvwchic wrote:

    > 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;

    --
    Fred
    Please respond only to this newsgroup.
    I do not reply to personal e-mail
     
  5. kerry_ja@yahoo.com

    kerry_ja@yahoo.com
    Expand Collapse
    Guest

    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
     

Share This Page