calculating Sick pay entitlement

[Odin]

Hi

I am trying to calculate the entitlement to Occupational (company)
sick pay (different and sepaarate to Statutory sick pay), using excel
for members in my team.

Our entitlement depends on length of employment i.e:

Under 12 months:
Entitlment to full pay = 0
Entitlent to half pay = 0

12 months to 2 years:
Entitlment to full pay = 1 month
Entitlent to half pay = 1 month

2 years to 5 years:
Entitlment to full pay = 3 months
Entitlent to half pay = 3 months

Over 5 years:
Entitlment to full pay = 6 months
Entitlent to half pay = 6 months

I am working this out from the date they started working (Service
reference date)

A working day is 7.5hrs, 5 days a week (1950 hrs a year) pro rataed
for part time working.

I have failed using nested formualae and was hoping that VBA code
might be the way but 'am hopeless at that.

Any help, clues, pointers would be appreciated.

 

[Jim Cone]

Are you trying to determine the length of employment or the duration of the employee's entitlement
pay?
'---
Jim Cone
Portland, Oregon USA .
http://www.contextures.com/excel-sort-addin.html .
(editorial review of special sort add-in (30 ways to sort)




"Odin" <[email protected]>
wrote in message
news:[email protected]...

 

[Ron Rosenfeld]

Hi

I am trying to calculate the entitlement to Occupational (company)
sick pay (different and sepaarate to Statutory sick pay), using excel
for members in my team.

Our entitlement depends on length of employment i.e:

Under 12 months:
Entitlment to full pay = 0
Entitlent to half pay = 0

12 months to 2 years:
Entitlment to full pay = 1 month
Entitlent to half pay = 1 month

2 years to 5 years:
Entitlment to full pay = 3 months
Entitlent to half pay = 3 months

Over 5 years:
Entitlment to full pay = 6 months
Entitlent to half pay = 6 months

I am working this out from the date they started working (Service
reference date)

A working day is 7.5hrs, 5 days a week (1950 hrs a year) pro rataed
for part time working.

I have failed using nested formualae and was hoping that VBA code
might be the way but 'am hopeless at that.

Any help, clues, pointers would be appreciated.

It is not entirely clear what your rules are for performing these calculations. But perhaps the following concepts will help.

To determine the time between two dates, you can use a formula like:

=DATEDIF(ServiceReferenceDate,Today,"m")

DATEDIF is a function that has been present in Excel for many versions, but only documented in HELP in XL2000. It has some flaws, but should be useful for this purpose; at least until you provide further information. It is documented here: http://www.cpearson.com/excel/datedif.aspx

Note that "years" and "months" are imprecise as they can each have varying numbers of days.

Once you know the number of months worked, you can then use a simple lookup table to determine the results:

For example:
in F1:G4
0 0
12 1
24 3
60 6

A formula to compute the entitlement:

=VLOOKUP(DATEDIF(ServiceReferenceDate,Today,"m"),$F$1:$G$4,2)