K
kabimeister
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
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