Aug 25 2022 01:03 PM - edited Aug 25 2022 01:04 PM
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.
Aug 25 2022 09:45 PM
@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.
Aug 26 2022 02:06 AM
This uses 365 for the spilt ranges but could be done without
= COUNTIFS(
EmpID, reportEmp#,
Week, reportWeek#)