Calculating Hours For Shift Penalties

[Cam_M]

Hi i'm new to this forum and would like some help with some formulas in calculating shift hours.

Thank you to everyone who has posted in the past these forums have helped me a lot.

[aydeegee]

Hi Cam_M

Unfortunately when I opened your file I have received a message from MS Office saying that it may be unsafe to edit your file (Never received such a message before!). You need to submit another file which has been cleaned by some antivirus software

Regards
Alastair

[Cam_M]

Thanks, I checked the file and its OK but it is read only protected no virus. Sorry

Having a hard time finding a formula for a work time sheet. It’s for a 24/7 roster so the hours are all over the place. There are only a few rules.

Any shift with hours outside of 0600 – 1800 but not past 0000 (midnight) will have normal hours + an afternoon penalty rate.
Any shift with hours past 0000 (midnight) will have normal hours + a night penalty rate.
Any hours on a weekend are weekend rates. (This rate will replace any other rate during all weekend hours)

So I need a formula to calculate how many hours of each there are for any one shift of anytime length.

Examples (12hr)

Monday 0600 - 1800 = 12hrs of Normal Rate.
(Because the hours are between 0600 – 1800)

Tuesday 0700 – 1900 = 12hrs of Normal Rate + 12hrs of Afternoon Penalty.
(Because the hours are outside of 0600 – 1800)

Wednesday 1400 – 0200 = 12hrs of Normal Rate + 12hrs of Night Penalty.
(Because the hours past over 0000)

Friday (into Saturday) 1900 – 0700 = 5hrs of Normal Rate + 5hrs of Night Penalty + 7hrs of Weekend Rate.
(Because 5hrs are in the Friday and the remainder are on the weekend)

Saturday 0700 – 1900 = 12hrs of Weekend Rate
(Because all hours are on a weekend)

Sunday (into Monday) 1400 – 0200 = 10hrs of Weekend Rate + 2hrs of Normal Rate + 2hrs of Night Penalty.
(Because 10hrs are in the weekend and the remainder are on a Monday morning. Also have 2hrs of night penalty rate, because the shift is past 0000 or midnight)

(These are for the above shifts) (Looking for the formulas for Normal, Afternoon, Night, Weekend)

Day / Shift / Normal / Afternoon / Night / Weekend

Monday 0600 – 1800 / 12 / 0 / 0 / 0

Tuesday 0700 – 1900 / 12 / 12 / 0 / 0

Wednesday 1400 – 0200 / 12 / 0 / 12 / 0

Friday 1900 – 0700 / 5 / 5 / 0 / 7

Saturday 0700 – 1900 / 0 / 0 / 0 / 12

Sunday 1400 – 0200 - 2 / 0 / 2 / 10

Looking for something like this or just some tips please.

Thanks in Advance.