Need help with a excel formula which will show how many consecutive days within a week an EE worked

Occasional Visitor

I need to identify how many days within a week an employee worked so we can flag anyone with more than 4 shifts per week.  Im using date worked and the calendar week of the year to try and calculate the number:




There are also 4 more columns (not shown here) with personal information.


2 Replies

@LCS I suggest you create a pivot table. EmployeeID in the Row field, WeekNr in the Column filed and EmployeeID in the Value field. Since the employee ID's seem to be texts already, the pivot table will count the number of occurrences for each employee by week, giving you an instant overview without the need for complicated formulas. Remove Grand Totals and or Sub Totals if not needed.


This uses 365 for the spilt ranges but could be done without

     EmpID, reportEmp#,
     Week,  reportWeek#)