Formula to calculate the hours in a range that fall between different time categories

[Faith14Hope]

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

[Kaper]

1) Let's start with G12. If you do expect that person, who started work yesterday at 9PM and finished today 4AM writes hours of start and finish in the same row then checkin C<B is important and shall lead to:

Please Login or Register  to view this content.

otherwise probably:

Please Login or Register  to view this content.

would be enough (I limited checking only to TimeOut, because as long as time out cell is empty there is no data to calculate time, and (probably ;-) ) nobody will write only time out and leave empty time in. (You can setup a conditional formatting to alert a user about such situation)

[Kaper]

Then H12:

Please Login or Register  to view this content.

I12:

Please Login or Register  to view this content.

J12:

Please Login or Register  to view this content.

K12:

Please Login or Register  to view this content.

and similar but with day = 7 in L12

[Faith14Hope]

Hi Kaper,

Thank you so much for your quick response and solutions to my problem, your formula's worked perfectly.
I agree with your assessment of the formula I was using in G12. I have simplified it using your second option.

Thanks again,
F14H

[FDibbins]

(You can setup a conditional formatting to alert a user about such situation)

or maybe Data Validation so you cannot enter a tome Out if there is no time IN