Work roster to count number of occassions each person worked pass 6pm for the week

[vc213]

Hi,

I have a weekly work roster and i'm trying to count the number of instances each staff member has worked passed 6pm during the week. I need a function that would count only if the time is greater than 6pm and the criteria for the employee name is met.

example of where the data is in the cells

B4 & C4 (merged cells) is employees name (drop down list)
B5 is start time e.g. 09:30 hours
C5 is finish time e.g. 18:30 hours
D5 is Meal break length time e.g. 01:00 hour
D4 is formula to calculate total hours worked in the shift. (I'm using a SUMIF function elsewhere to sum the total weekly hors for each employee)

I thought COUNTIFS function with criteria 1 being “>18:00” and criteria 2 being the “employees name” . I can’t get this work though as the employees name is in a cell above and to the left of the cell containing the first criteria. I then thought I could maybe try OFFSET but can’t get this to work.

My excel skills are very limited so I would really appreciate help on this.

[Ursul]

Attached is a Timetable I set up some years ago and changed the hours to fit your needs.

Col B is a Drop-down where you select one of some 6 or so choices. If you select "Normal" then the hours will fill automatically. If someone works overtime then the 'Time-Out' must be changed manually - that is if someone works until 7:30 pm then you must enter '19:00' (in that format!!!)

TIMESHEET EXAMPLE.xlsx

Cheers,

[Ursul]

Sorry just saw that you are using Excel 2003.

TIMESHEET EXAMPLE.xls