Hi everyone,

I would be eternally grateful if anyone is able to offer a solution to this problem.

I have created the attached timesheet based on the hospitality industry award rates (Aus.), where any hours worked between 7am and 7pm are 'Normal', hours between 7pm and middnight are 'Evening' and middnight to 7am is 'Late'. Saturday, Sunday and Public Holidays are then a different rate altogether.

Basically I want the sheet to calculate the number of hours for each category based on the total hours worked by the employee. I am trying to eliminate human error by only requiring the hours worked to be entered.

I have seen several other threads with queries similar to mine, based on Ordinary hours vs overtime, and I have tried those formulas in my sheet but they aren't working. I don't know how I am messing it up - I'm not a newbie to excel, but I seem to be suffering from a brain fart as far as this is concerned

Any help would be muchly appreciated



BGSA Timesheet.xlsx