I have attached a spreadsheet that details all employees that have entered a timesheet for each day for the week commencing 24th March on the 'AllData' tab. I have also listed the employees that belong to a particular department on the 'Staff_Details' tab.
My objective is to achieve the following:
1. Identify those staff in the department stated on the 'Staff_Details' tab who simply have not entered a time sheet at all and list those days where a time sheet has been missed for each employee
2. Identify in a separate list, those employees who have incorrectly entered a timesheet (wrong days, number of hours for the week, number of hours for a particular day)
Rules include the following:
a. the total hours entered for each week
(mon to saturday) for each employee does NOT equal or exceed
the total expected hours in Col C of the 'Staff_Details' sheet
b. the hours entered for each employee on a particular day
does not equal days on which each employee should not
work as detailed in columns D to J of the 'Staff_Details' sheet
c. the hours entered for each employee on a particular day
is not correct (i.e. less than the stated total weekly hours in Col C of the 'Staff_Details' tab divided by the number of days that they are expected to work as detailed in columns D to J on the 'Staff_Details' tab)
d. the 'Activity Status' of the time entered shoud read 'Submitted' (see table AllData column M)
3. The output report shoud clearly identify each employee that is not compliant and show those days where the timesheet is missing or defective per item 2 above
4. The output report should have a row per employee stated in the department on the 'Staff_Details' tab and show the total time (hours) worked per day in accordance with the 'rules' above.
i. The time on the 'AllData' sheet is expressed in minutes so will need to be converted to hours in terms of time worked.
ii. Employees may make more than one entry on a particular day e.g. for each activity
Thanks in advance,