Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
calculating Sick pay entitlement
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
calculating Sick pay entitlement
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" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
calculating Sick pay entitlement
On Sun, 11 Sep 2011 08:35:14 -0700 (PDT), Odin wrote:
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 he 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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sick time usage | Excel Discussion (Misc queries) | |||
Establishing Annual Leave Entitlement | Excel Discussion (Misc queries) | |||
Various OT Entitlement | Excel Discussion (Misc queries) | |||
IF,or IF--so sick of IF's | Excel Worksheet Functions | |||
sick code needs doctor | Excel Programming |