Based on attendance calculate overtime and absent hours in seperate sheet

Good Morning Everyone,

I have a data in which I calculate the overtime and absenteeism manually for every employee based on the below conditions.

1. Staff should work for 12 hours per day.
2. More than 12 hours worked is treated as overtime.
3. Less than 12 hours worked treated as absent hours.
4. Staff is eligible for one day off per week (rotational)Every seventh day from last off taken is off day.
5. If the staff has worked on 7th day after last off will be treated as overtime.
6. Absent is marked as "A" and treated as 12 hours absent.
I need to capture the summary date wise, staff wise in a different sheet by matching above criteria. I have attached the example file which I have calculated.
Please help if the same can be done in a smarter way.

Thank you,
Skand Kamat

12 hours a day, 6 days a week, all a standard pay; those are onerous working conditions. The irony is you think your job is too hard.

Originally Posted by AlphaFrog

12 hours a day, 6 days a week, all a standard pay; those are onerous working conditions. The irony is you think your job is too hard.

I didnt say my job is too hard Mr. Frog. I just asked is there a smarter or better way to do it. I am sure this can be done, as I am new to VBA or Macro asked for guidence.

This will be in the form of "some help" as in making the identification of OT and Abs hours more automatic on the 'Attendance' sheet.
The range AH3:BE44 is populated using: =AND(SUMPRODUCT(--(ISNUMBER(D3:J3)))=7,COUNTIF(AB3:AG3,FALSE)=6)
The following formula is applied as conditional formatting to J3:AG44 =AH3=TRUE (fill = yellow)
The following formulas are applied as conditional formatting to D3:AG44
1) =AND(ISNUMBER(D3),D3>12) (fill = blue)
2) =AND(ISNUMBER(D3),D3<12) (font = red)
Let us know if you have any questions.