Date Calculations

[claddagh]

Hi Guys,

Just a quick request for a bit of help.
Tries a few things but didnt work.

I have a form which has an employee start date.
What I need to do is work out the holiday entitlement of each emplyee.
The math behine this is, if they have been here less than a year,
we do the following.
20 Days holiday entitlement per year, Divided by 52, then multiplyed by the ammount of weeks remaining in the financial year.
eg, (20 / 52 = 0.384615384) * 31 = 11.92
So they would have 12 Days holiday entitlement.

But if they have been here for 12 months or more, they are entitled to the full 20 days holiday.

How would I express this as an expression or query or VBA to calculate this.
I just cant figure it out, especially the if been here 12 months or more.

I have the start date, in a tbl called tbl_employees if that helps. ( also in the form frm_employee_details )
Our financial Year runs from the 1st December - 31st November

Thanks in advance for any help / advice


Max

 

[ejstefl]

A couple of questions:

1) "20 Days holiday entitlement per year, Divided by 52, then multiplyed by the ammount of weeks remaining in the financial year." I assume you mean the number of weeks remaining between the start date and the number of weeks remaining?

2) If the employee started 11/15/04, how many vacation days should they have?

This seems to work in most cases...

If Date - Me.dtStart >= 365 Then

    Me.txtVacation = 20
Else
     Me.txtVacation = (20 / 52) * DateDiff("w", Me.dtStart, "11/30")
    
End If

 

[claddagh]

Looks Good

Thanks for that.
Looks like itll do the job.

I`ll test and get back to you.

Max