Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating occupational sick pay entitlement
Hi People,
I'd like some help with automaticall working out at what rate an emplyee is due occupational sick pay (currently I am doing this manually). An employee is entitled to occupational sick pay of 2 months at Full pay and a further 2 months at Half pay and then Nill pay in a rolling 12 months. Assume that the employee works 7.5 hours a day for 37.5 hours a week. I'd like help with IF, AND, OR (nested)formulae (or any other VBA solution) that I can use to automatically notify me when the employee is due full/half/nill pay given the day and number of hours off sick. Example: On 01 Feb 2012 Laura had 3 occasions when she'd been sick: 01 Jan 2011 to 31 Mar 2011 01 Apr 2011 to 15 May 2011 01 Nov 2011 to 31 Jan 2012 In this instance she was due Full pay for 2 month (1st month 01 Jan 2011 to 31 Jan; and second month 01 Feb to 28 Feb 2011) Half pay for 2 month (1st month 01 Mar 2011 to 31 Mar 2011; and second month 01 Apr 2011 to 30 Apr 2011) Nill pay for the rest until the 01 Jan 2012 then back to full pay from 02 Jan 2012 to 31 Jan 2012 (new rolling year) The data provided is in this format: Name Unique ID Date Hrs Sick Laura 1234 Sat 01/01/2011 00:00:00 Laura 1234 Sun 02/01/2011 07:30:00 Laura 1234 Mon 03/01/2011 07:30:00 Laura 1234 Tue 04/01/2011 07:30:00 Laura 1234 Wed 05/01/2011 07:30:00 Laura 1234 Thu 06/01/2011 07:30:00 Laura 1234 Fri 07/01/2011 00:00:00 Laura 1234 Sat 08/01/2011 00:00:00 Laura 1234 Sun 09/01/2011 07:30:00 Laura 1234 Mon 10/01/2011 07:30:00 Laura 1234 Tue 11/01/2011 07:30:00 Laura 1234 Wed 12/01/2011 07:30:00 Laura 1234 Thu 13/01/2011 07:30:00 Laura 1234 Fri 14/01/2011 00:00:00 Laura 1234 Sat 15/01/2011 00:00:00 Laura 1234 Sun 16/01/2011 07:30:00 Laura 1234 Mon 17/01/2011 07:30:00 Laura 1234 Tue 18/01/2011 07:30:00 Laura 1234 Wed 19/01/2011 07:30:00 Laura 1234 Thu 20/01/2011 07:30:00 I'd like the raw data (which will be cumulative as the occassions happen) on one tab and the results on another indicating a date of eligibility and rate. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
calculating Sick pay entitlement | Excel Programming | |||
Establishing Annual Leave Entitlement | Excel Discussion (Misc queries) | |||
Various OT Entitlement | Excel Discussion (Misc queries) | |||
Sick time accumulation | Excel Worksheet Functions | |||
IF,or IF--so sick of IF's | Excel Worksheet Functions |