BobFossil

10-24-2016, 06:31 AM

Hello VBA Express, i hope you can help with this. I have to compile 'hours worked' for members of staff in a healthcare environment. However, the situation is complicated by 'when' in both hours and date range that a member of staff works and i am stumped as to how to calculate the numbers without a lot of manual sifting.

My core (or normal) working week hours are 08:00:00 AM to 20:00:00 PM Monday to Friday. Any calls or visits made between 20:00:00 PM and 08:00:00 AM attract an enhanced pay level, similarly Weekends are from 20:00:00 PM on Friday to 08:00:00 AM on Monday and attract this enhancement as well. I also have to consider Bank Holidays but i am ok with this aspect.

What i can't figure out how to do is calculate the 'normal' hours and 'enhanced payment' hours. On-call or emergency visits don't have any set pattern and can be a combination of normal/enhanced or workday/weekend/Bank Holiday

If a member of staff starts work at 19:30 pm and finishes at 22:00:00 pm, that is half a 'normal' hour and 2 enhanced hours.

If a member of staff starts at 03:15:00 am and finishes at 09:45 am, that is 4.75 enhanced hours and 1.75 normal hours.

Where hours are solely within either category, Normal or Enhanced, the formula is relatively simple, it is the cross over values that i can't figure out the formula for. I have attached a sample of the timesheet data

i am hoping that one of you will know of a procedure or technique for doing this with a VBA Macro or somesuch as i can't get this last piece of the puzzle. I have on average 4000 rows of work hours to calculate each month so would be grateful if you can help at all.

Regards,

Rob

My core (or normal) working week hours are 08:00:00 AM to 20:00:00 PM Monday to Friday. Any calls or visits made between 20:00:00 PM and 08:00:00 AM attract an enhanced pay level, similarly Weekends are from 20:00:00 PM on Friday to 08:00:00 AM on Monday and attract this enhancement as well. I also have to consider Bank Holidays but i am ok with this aspect.

What i can't figure out how to do is calculate the 'normal' hours and 'enhanced payment' hours. On-call or emergency visits don't have any set pattern and can be a combination of normal/enhanced or workday/weekend/Bank Holiday

If a member of staff starts work at 19:30 pm and finishes at 22:00:00 pm, that is half a 'normal' hour and 2 enhanced hours.

If a member of staff starts at 03:15:00 am and finishes at 09:45 am, that is 4.75 enhanced hours and 1.75 normal hours.

Where hours are solely within either category, Normal or Enhanced, the formula is relatively simple, it is the cross over values that i can't figure out the formula for. I have attached a sample of the timesheet data

i am hoping that one of you will know of a procedure or technique for doing this with a VBA Macro or somesuch as i can't get this last piece of the puzzle. I have on average 4000 rows of work hours to calculate each month so would be grateful if you can help at all.

Regards,

Rob