I am looking to perform a calculation to ascertain an employees overtime hours per week. There are a number of factors that I need to take into consideration when calculating:

1. The number of hours per day is 8, and the number of hours per week is 40
2. Overtime only kicks in if the hours number of hours for the day is greater than 8 AND the number of hours in a week is greater than 40
3. Consideration is required for the employee taking a Personal Holiday, a Sick Day or if there is a Public Holiday. If any or some of these are the case, then the total number of hours in the week should be reduced by a day (8 hours) for each day that the employee is out. So if for example there is a Bank holiday and they have taken a days holiday, the total number of hours in the week would be 24, and so any hours over 8 per day and 24 per week should be calculated and displayed in the Overtime hours

The calculation should be dynamic and is based of the column entitled "Week Number".

Please see the screenshot of the worksheet I am using:

Table updated with Data below, now showing a week with overtime, and also saturday work: (humbelest apologies for the stat that this looks, but I am still learning in this space...)

Index	Week Number	Month	Year	Employee Name	Date	Day	Week Day Number	Time In	Time Out	Regular Hours	Overtime Hours	Total Hours	Sick days / Force Major	Personal Holiday	Public Holiday	Comment
2025_1_3	1	January	2025	Test Employee	01/01/2025	Wednesday	4			=IF(AND([@[Time In]]="",[@[Time Out]]=""),"",(IF((IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-0.5)>8,8,(IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-0.5))))	=IF((IF([@[Time In]]="","",(IF([@[Regular Hours]]<8,0,(IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-8.5)))))<0,0,(IF([@[Time In]]="","",(IF([@[Regular Hours]]<8,0,(IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-8.5))))))	=IF(OR([@[Sick Days / Force Major]]=1,[@[Personal Holiday]]=1,[@[Public Holiday]]=1),8,IF(OR([@[Sick Days / Force Major]]=0.5,[@[Personal Holiday]]=0.5,[@[Public Holiday]]=0.5),4,(IF([@[Time In]]="","",(SUM([@[Regular Hours]],[@[Overtime Hours]]))))))	=(IF(OR(LEFT([@Comment],4)="Sick",LEFT([@Comment],4)="FoMa"),1,""))	=(IF(LEFT([@Comment],4)="Full",1,IF(LEFT([@Comment],4)="Half",0.5,"")))	=(IF(LEFT([@Comment],5)="BkHol",1,""))
2025_1_4	1	January	2025	Test Employee	02/01/2025	Thursday	5			=IF(AND([@[Time In]]="",[@[Time Out]]=""),"",(IF((IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-0.5)>8,8,(IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-0.5))))	=IF((IF([@[Time In]]="","",(IF([@[Regular Hours]]<8,0,(IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-8.5)))))<0,0,(IF([@[Time In]]="","",(IF([@[Regular Hours]]<8,0,(IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-8.5))))))	=IF(OR([@[Sick Days / Force Major]]=1,[@[Personal Holiday]]=1,[@[Public Holiday]]=1),8,IF(OR([@[Sick Days / Force Major]]=0.5,[@[Personal Holiday]]=0.5,[@[Public Holiday]]=0.5),4,(IF([@[Time In]]="","",(SUM([@[Regular Hours]],[@[Overtime Hours]]))))))	=(IF(OR(LEFT([@Comment],4)="Sick",LEFT([@Comment],4)="FoMa"),1,""))	=(IF(LEFT([@Comment],4)="Full",1,IF(LEFT([@Comment],4)="Half",0.5,"")))	=(IF(LEFT([@Comment],5)="BkHol",1,""))
2025_1_5	1	January	2025	Test Employee	03/01/2025	Friday	6			=IF(AND([@[Time In]]="",[@[Time Out]]=""),"",(IF((IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-0.5)>8,8,(IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-0.5))))	=IF((IF([@[Time In]]="","",(IF([@[Regular Hours]]<8,0,(IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-8.5)))))<0,0,(IF([@[Time In]]="","",(IF([@[Regular Hours]]<8,0,(IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-8.5))))))	=IF(OR([@[Sick Days / Force Major]]=1,[@[Personal Holiday]]=1,[@[Public Holiday]]=1),8,IF(OR([@[Sick Days / Force Major]]=0.5,[@[Personal Holiday]]=0.5,[@[Public Holiday]]=0.5),4,(IF([@[Time In]]="","",(SUM([@[Regular Hours]],[@[Overtime Hours]]))))))	=(IF(OR(LEFT([@Comment],4)="Sick",LEFT([@Comment],4)="FoMa"),1,""))	=(IF(LEFT([@Comment],4)="Full",1,IF(LEFT([@Comment],4)="Half",0.5,"")))	=(IF(LEFT([@Comment],5)="BkHol",1,""))
2025_1_6	1	January	2025	Test Employee	04/01/2025	Saturday	7			=IF(AND([@[Time In]]="",[@[Time Out]]=""),"",(IF((IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-0.5)>8,8,(IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-0.5))))	=IF((IF([@[Time In]]="","",(IF([@[Regular Hours]]<8,0,(IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-8.5)))))<0,0,(IF([@[Time In]]="","",(IF([@[Regular Hours]]<8,0,(IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-8.5))))))	=IF(OR([@[Sick Days / Force Major]]=1,[@[Personal Holiday]]=1,[@[Public Holiday]]=1),8,IF(OR([@[Sick Days / Force Major]]=0.5,[@[Personal Holiday]]=0.5,[@[Public Holiday]]=0.5),4,(IF([@[Time In]]="","",(SUM([@[Regular Hours]],[@[Overtime Hours]]))))))	=(IF(OR(LEFT([@Comment],4)="Sick",LEFT([@Comment],4)="FoMa"),1,""))	=(IF(LEFT([@Comment],4)="Full",1,IF(LEFT([@Comment],4)="Half",0.5,"")))	=(IF(LEFT([@Comment],5)="BkHol",1,""))
2025_1_7	1	January	2025	Test Employee	05/01/2025	Sunday	1			=IF(AND([@[Time In]]="",[@[Time Out]]=""),"",(IF((IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-0.5)>8,8,(IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-0.5))))	=IF((IF([@[Time In]]="","",(IF([@[Regular Hours]]<8,0,(IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-8.5)))))<0,0,(IF([@[Time In]]="","",(IF([@[Regular Hours]]<8,0,(IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-8.5))))))	=IF(OR([@[Sick Days / Force Major]]=1,[@[Personal Holiday]]=1,[@[Public Holiday]]=1),8,IF(OR([@[Sick Days / Force Major]]=0.5,[@[Personal Holiday]]=0.5,[@[Public Holiday]]=0.5),4,(IF([@[Time In]]="","",(SUM([@[Regular Hours]],[@[Overtime Hours]]))))))	=(IF(OR(LEFT([@Comment],4)="Sick",LEFT([@Comment],4)="FoMa"),1,""))	=(IF(LEFT([@Comment],4)="Full",1,IF(LEFT([@Comment],4)="Half",0.5,"")))	=(IF(LEFT([@Comment],5)="BkHol",1,""))
2025_2_1	2	January	2025	Test Employee	06/01/2025	Monday	2	8:30	18:00	=IF(AND([@[Time In]]="",[@[Time Out]]=""),"",(IF((IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-0.5)>8,8,(IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-0.5))))	=IF((IF([@[Time In]]="","",(IF([@[Regular Hours]]<8,0,(IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-8.5)))))<0,0,(IF([@[Time In]]="","",(IF([@[Regular Hours]]<8,0,(IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-8.5))))))	=IF(OR([@[Sick Days / Force Major]]=1,[@[Personal Holiday]]=1,[@[Public Holiday]]=1),8,IF(OR([@[Sick Days / Force Major]]=0.5,[@[Personal Holiday]]=0.5,[@[Public Holiday]]=0.5),4,(IF([@[Time In]]="","",(SUM([@[Regular Hours]],[@[Overtime Hours]]))))))	=(IF(OR(LEFT([@Comment],4)="Sick",LEFT([@Comment],4)="FoMa"),1,""))	=(IF(LEFT([@Comment],4)="Full",1,IF(LEFT([@Comment],4)="Half",0.5,"")))	=(IF(LEFT([@Comment],5)="BkHol",1,""))
2025_2_2	2	January	2025	Test Employee	07/01/2025	Tuesday	3	8:30	19:00	=IF(AND([@[Time In]]="",[@[Time Out]]=""),"",(IF((IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-0.5)>8,8,(IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-0.5))))	=IF((IF([@[Time In]]="","",(IF([@[Regular Hours]]<8,0,(IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-8.5)))))<0,0,(IF([@[Time In]]="","",(IF([@[Regular Hours]]<8,0,(IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-8.5))))))	=IF(OR([@[Sick Days / Force Major]]=1,[@[Personal Holiday]]=1,[@[Public Holiday]]=1),8,IF(OR([@[Sick Days / Force Major]]=0.5,[@[Personal Holiday]]=0.5,[@[Public Holiday]]=0.5),4,(IF([@[Time In]]="","",(SUM([@[Regular Hours]],[@[Overtime Hours]]))))))	=(IF(OR(LEFT([@Comment],4)="Sick",LEFT([@Comment],4)="FoMa"),1,""))	=(IF(LEFT([@Comment],4)="Full",1,IF(LEFT([@Comment],4)="Half",0.5,"")))	=(IF(LEFT([@Comment],5)="BkHol",1,""))
2025_2_3	2	January	2025	Test Employee	08/01/2025	Wednesday	4	8:30	17:00	=IF(AND([@[Time In]]="",[@[Time Out]]=""),"",(IF((IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-0.5)>8,8,(IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-0.5))))	=IF((IF([@[Time In]]="","",(IF([@[Regular Hours]]<8,0,(IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-8.5)))))<0,0,(IF([@[Time In]]="","",(IF([@[Regular Hours]]<8,0,(IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-8.5))))))	=IF(OR([@[Sick Days / Force Major]]=1,[@[Personal Holiday]]=1,[@[Public Holiday]]=1),8,IF(OR([@[Sick Days / Force Major]]=0.5,[@[Personal Holiday]]=0.5,[@[Public Holiday]]=0.5),4,(IF([@[Time In]]="","",(SUM([@[Regular Hours]],[@[Overtime Hours]]))))))	=(IF(OR(LEFT([@Comment],4)="Sick",LEFT([@Comment],4)="FoMa"),1,""))	=(IF(LEFT([@Comment],4)="Full",1,IF(LEFT([@Comment],4)="Half",0.5,"")))	=(IF(LEFT([@Comment],5)="BkHol",1,""))
2025_2_4	2	January	2025	Test Employee	09/01/2025	Thursday	5	8:30	17:00	=IF(AND([@[Time In]]="",[@[Time Out]]=""),"",(IF((IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-0.5)>8,8,(IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-0.5))))	=IF((IF([@[Time In]]="","",(IF([@[Regular Hours]]<8,0,(IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-8.5)))))<0,0,(IF([@[Time In]]="","",(IF([@[Regular Hours]]<8,0,(IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-8.5))))))	=IF(OR([@[Sick Days / Force Major]]=1,[@[Personal Holiday]]=1,[@[Public Holiday]]=1),8,IF(OR([@[Sick Days / Force Major]]=0.5,[@[Personal Holiday]]=0.5,[@[Public Holiday]]=0.5),4,(IF([@[Time In]]="","",(SUM([@[Regular Hours]],[@[Overtime Hours]]))))))	=(IF(OR(LEFT([@Comment],4)="Sick",LEFT([@Comment],4)="FoMa"),1,""))	=(IF(LEFT([@Comment],4)="Full",1,IF(LEFT([@Comment],4)="Half",0.5,"")))	=(IF(LEFT([@Comment],5)="BkHol",1,""))
2025_2_5	2	January	2025	Test Employee	10/01/2025	Friday	6	8:30	17:00	=IF(AND([@[Time In]]="",[@[Time Out]]=""),"",(IF((IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-0.5)>8,8,(IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-0.5))))	=IF((IF([@[Time In]]="","",(IF([@[Regular Hours]]<8,0,(IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-8.5)))))<0,0,(IF([@[Time In]]="","",(IF([@[Regular Hours]]<8,0,(IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-8.5))))))	=IF(OR([@[Sick Days / Force Major]]=1,[@[Personal Holiday]]=1,[@[Public Holiday]]=1),8,IF(OR([@[Sick Days / Force Major]]=0.5,[@[Personal Holiday]]=0.5,[@[Public Holiday]]=0.5),4,(IF([@[Time In]]="","",(SUM([@[Regular Hours]],[@[Overtime Hours]]))))))	=(IF(OR(LEFT([@Comment],4)="Sick",LEFT([@Comment],4)="FoMa"),1,""))	=(IF(LEFT([@Comment],4)="Full",1,IF(LEFT([@Comment],4)="Half",0.5,"")))	=(IF(LEFT([@Comment],5)="BkHol",1,""))
2025_2_6	2	January	2025	Test Employee	11/01/2025	Saturday	7	8:30	15:45	=IF(AND([@[Time In]]="",[@[Time Out]]=""),"",(IF((IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-0.5)>8,8,(IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-0.5))))	=IF((IF([@[Time In]]="","",(IF([@[Regular Hours]]<8,0,(IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-8.5)))))<0,0,(IF([@[Time In]]="","",(IF([@[Regular Hours]]<8,0,(IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-8.5))))))	=IF(OR([@[Sick Days / Force Major]]=1,[@[Personal Holiday]]=1,[@[Public Holiday]]=1),8,IF(OR([@[Sick Days / Force Major]]=0.5,[@[Personal Holiday]]=0.5,[@[Public Holiday]]=0.5),4,(IF([@[Time In]]="","",(SUM([@[Regular Hours]],[@[Overtime Hours]]))))))	=(IF(OR(LEFT([@Comment],4)="Sick",LEFT([@Comment],4)="FoMa"),1,""))	=(IF(LEFT([@Comment],4)="Full",1,IF(LEFT([@Comment],4)="Half",0.5,"")))	=(IF(LEFT([@Comment],5)="BkHol",1,""))
2025_2_7	2	January	2025	Test Employee	12/01/2025	Sunday	1			=IF(AND([@[Time In]]="",[@[Time Out]]=""),"",(IF((IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-0.5)>8,8,(IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-0.5))))	=IF((IF([@[Time In]]="","",(IF([@[Regular Hours]]<8,0,(IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-8.5)))))<0,0,(IF([@[Time In]]="","",(IF([@[Regular Hours]]<8,0,(IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-8.5))))))	=IF(OR([@[Sick Days / Force Major]]=1,[@[Personal Holiday]]=1,[@[Public Holiday]]=1),8,IF(OR([@[Sick Days / Force Major]]=0.5,[@[Personal Holiday]]=0.5,[@[Public Holiday]]=0.5),4,(IF([@[Time In]]="","",(SUM([@[Regular Hours]],[@[Overtime Hours]]))))))	=(IF(OR(LEFT([@Comment],4)="Sick",LEFT([@Comment],4)="FoMa"),1,""))	=(IF(LEFT([@Comment],4)="Full",1,IF(LEFT([@Comment],4)="Half",0.5,"")))	=(IF(LEFT([@Comment],5)="BkHol",1,""))

I am looking to perform a calculation to ascertain an employees overtime hours per week. There are a number of factors that I need to take into consideration when calculating:

1. The number of hours per day is 8, and the number of hours per week is 40
2. Overtime only kicks in if the hours number of hours for the day is greater than 8 AND the number of hours in a week is greater than 40
3. Consideration is required for the employee taking a Personal Holiday, a Sick Day or if there is a Public Holiday. If any or some of these are the case, then the total number of hours in the week should be reduced by a day (8 hours) for each day that the employee is out. So if for example there is a Bank holiday and they have taken a days holiday, the total number of hours in the week would be 24, and so any hours over 8 per day and 24 per week should be calculated and displayed in the Overtime hours

The calculation should be dynamic and is based of the column entitled "Week Number".

Please see the screenshot of the worksheet I am using:

Table updated with Data below, now showing a week with overtime, and also saturday work: (humbelest apologies for the stat that this looks, but I am still learning in this space...)

Index	Week Number	Month	Year	Employee Name	Date	Day	Week Day Number	Time In	Time Out	Regular Hours	Overtime Hours	Total Hours	Sick days / Force Major	Personal Holiday	Public Holiday	Comment
2025_1_3	1	January	2025	Test Employee	01/01/2025	Wednesday	4			=IF(AND([@[Time In]]="",[@[Time Out]]=""),"",(IF((IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-0.5)>8,8,(IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-0.5))))	=IF((IF([@[Time In]]="","",(IF([@[Regular Hours]]<8,0,(IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-8.5)))))<0,0,(IF([@[Time In]]="","",(IF([@[Regular Hours]]<8,0,(IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-8.5))))))	=IF(OR([@[Sick Days / Force Major]]=1,[@[Personal Holiday]]=1,[@[Public Holiday]]=1),8,IF(OR([@[Sick Days / Force Major]]=0.5,[@[Personal Holiday]]=0.5,[@[Public Holiday]]=0.5),4,(IF([@[Time In]]="","",(SUM([@[Regular Hours]],[@[Overtime Hours]]))))))	=(IF(OR(LEFT([@Comment],4)="Sick",LEFT([@Comment],4)="FoMa"),1,""))	=(IF(LEFT([@Comment],4)="Full",1,IF(LEFT([@Comment],4)="Half",0.5,"")))	=(IF(LEFT([@Comment],5)="BkHol",1,""))
2025_1_4	1	January	2025	Test Employee	02/01/2025	Thursday	5			=IF(AND([@[Time In]]="",[@[Time Out]]=""),"",(IF((IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-0.5)>8,8,(IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-0.5))))	=IF((IF([@[Time In]]="","",(IF([@[Regular Hours]]<8,0,(IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-8.5)))))<0,0,(IF([@[Time In]]="","",(IF([@[Regular Hours]]<8,0,(IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-8.5))))))	=IF(OR([@[Sick Days / Force Major]]=1,[@[Personal Holiday]]=1,[@[Public Holiday]]=1),8,IF(OR([@[Sick Days / Force Major]]=0.5,[@[Personal Holiday]]=0.5,[@[Public Holiday]]=0.5),4,(IF([@[Time In]]="","",(SUM([@[Regular Hours]],[@[Overtime Hours]]))))))	=(IF(OR(LEFT([@Comment],4)="Sick",LEFT([@Comment],4)="FoMa"),1,""))	=(IF(LEFT([@Comment],4)="Full",1,IF(LEFT([@Comment],4)="Half",0.5,"")))	=(IF(LEFT([@Comment],5)="BkHol",1,""))
2025_1_5	1	January	2025	Test Employee	03/01/2025	Friday	6			=IF(AND([@[Time In]]="",[@[Time Out]]=""),"",(IF((IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-0.5)>8,8,(IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-0.5))))	=IF((IF([@[Time In]]="","",(IF([@[Regular Hours]]<8,0,(IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-8.5)))))<0,0,(IF([@[Time In]]="","",(IF([@[Regular Hours]]<8,0,(IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-8.5))))))	=IF(OR([@[Sick Days / Force Major]]=1,[@[Personal Holiday]]=1,[@[Public Holiday]]=1),8,IF(OR([@[Sick Days / Force Major]]=0.5,[@[Personal Holiday]]=0.5,[@[Public Holiday]]=0.5),4,(IF([@[Time In]]="","",(SUM([@[Regular Hours]],[@[Overtime Hours]]))))))	=(IF(OR(LEFT([@Comment],4)="Sick",LEFT([@Comment],4)="FoMa"),1,""))	=(IF(LEFT([@Comment],4)="Full",1,IF(LEFT([@Comment],4)="Half",0.5,"")))	=(IF(LEFT([@Comment],5)="BkHol",1,""))
2025_1_6	1	January	2025	Test Employee	04/01/2025	Saturday	7			=IF(AND([@[Time In]]="",[@[Time Out]]=""),"",(IF((IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-0.5)>8,8,(IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-0.5))))	=IF((IF([@[Time In]]="","",(IF([@[Regular Hours]]<8,0,(IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-8.5)))))<0,0,(IF([@[Time In]]="","",(IF([@[Regular Hours]]<8,0,(IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-8.5))))))	=IF(OR([@[Sick Days / Force Major]]=1,[@[Personal Holiday]]=1,[@[Public Holiday]]=1),8,IF(OR([@[Sick Days / Force Major]]=0.5,[@[Personal Holiday]]=0.5,[@[Public Holiday]]=0.5),4,(IF([@[Time In]]="","",(SUM([@[Regular Hours]],[@[Overtime Hours]]))))))	=(IF(OR(LEFT([@Comment],4)="Sick",LEFT([@Comment],4)="FoMa"),1,""))	=(IF(LEFT([@Comment],4)="Full",1,IF(LEFT([@Comment],4)="Half",0.5,"")))	=(IF(LEFT([@Comment],5)="BkHol",1,""))
2025_1_7	1	January	2025	Test Employee	05/01/2025	Sunday	1			=IF(AND([@[Time In]]="",[@[Time Out]]=""),"",(IF((IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-0.5)>8,8,(IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-0.5))))	=IF((IF([@[Time In]]="","",(IF([@[Regular Hours]]<8,0,(IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-8.5)))))<0,0,(IF([@[Time In]]="","",(IF([@[Regular Hours]]<8,0,(IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-8.5))))))	=IF(OR([@[Sick Days / Force Major]]=1,[@[Personal Holiday]]=1,[@[Public Holiday]]=1),8,IF(OR([@[Sick Days / Force Major]]=0.5,[@[Personal Holiday]]=0.5,[@[Public Holiday]]=0.5),4,(IF([@[Time In]]="","",(SUM([@[Regular Hours]],[@[Overtime Hours]]))))))	=(IF(OR(LEFT([@Comment],4)="Sick",LEFT([@Comment],4)="FoMa"),1,""))	=(IF(LEFT([@Comment],4)="Full",1,IF(LEFT([@Comment],4)="Half",0.5,"")))	=(IF(LEFT([@Comment],5)="BkHol",1,""))
2025_2_1	2	January	2025	Test Employee	06/01/2025	Monday	2	8:30	18:00	=IF(AND([@[Time In]]="",[@[Time Out]]=""),"",(IF((IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-0.5)>8,8,(IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-0.5))))	=IF((IF([@[Time In]]="","",(IF([@[Regular Hours]]<8,0,(IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-8.5)))))<0,0,(IF([@[Time In]]="","",(IF([@[Regular Hours]]<8,0,(IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-8.5))))))	=IF(OR([@[Sick Days / Force Major]]=1,[@[Personal Holiday]]=1,[@[Public Holiday]]=1),8,IF(OR([@[Sick Days / Force Major]]=0.5,[@[Personal Holiday]]=0.5,[@[Public Holiday]]=0.5),4,(IF([@[Time In]]="","",(SUM([@[Regular Hours]],[@[Overtime Hours]]))))))	=(IF(OR(LEFT([@Comment],4)="Sick",LEFT([@Comment],4)="FoMa"),1,""))	=(IF(LEFT([@Comment],4)="Full",1,IF(LEFT([@Comment],4)="Half",0.5,"")))	=(IF(LEFT([@Comment],5)="BkHol",1,""))
2025_2_2	2	January	2025	Test Employee	07/01/2025	Tuesday	3	8:30	19:00	=IF(AND([@[Time In]]="",[@[Time Out]]=""),"",(IF((IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-0.5)>8,8,(IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-0.5))))	=IF((IF([@[Time In]]="","",(IF([@[Regular Hours]]<8,0,(IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-8.5)))))<0,0,(IF([@[Time In]]="","",(IF([@[Regular Hours]]<8,0,(IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-8.5))))))	=IF(OR([@[Sick Days / Force Major]]=1,[@[Personal Holiday]]=1,[@[Public Holiday]]=1),8,IF(OR([@[Sick Days / Force Major]]=0.5,[@[Personal Holiday]]=0.5,[@[Public Holiday]]=0.5),4,(IF([@[Time In]]="","",(SUM([@[Regular Hours]],[@[Overtime Hours]]))))))	=(IF(OR(LEFT([@Comment],4)="Sick",LEFT([@Comment],4)="FoMa"),1,""))	=(IF(LEFT([@Comment],4)="Full",1,IF(LEFT([@Comment],4)="Half",0.5,"")))	=(IF(LEFT([@Comment],5)="BkHol",1,""))
2025_2_3	2	January	2025	Test Employee	08/01/2025	Wednesday	4	8:30	17:00	=IF(AND([@[Time In]]="",[@[Time Out]]=""),"",(IF((IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-0.5)>8,8,(IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-0.5))))	=IF((IF([@[Time In]]="","",(IF([@[Regular Hours]]<8,0,(IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-8.5)))))<0,0,(IF([@[Time In]]="","",(IF([@[Regular Hours]]<8,0,(IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-8.5))))))	=IF(OR([@[Sick Days / Force Major]]=1,[@[Personal Holiday]]=1,[@[Public Holiday]]=1),8,IF(OR([@[Sick Days / Force Major]]=0.5,[@[Personal Holiday]]=0.5,[@[Public Holiday]]=0.5),4,(IF([@[Time In]]="","",(SUM([@[Regular Hours]],[@[Overtime Hours]]))))))	=(IF(OR(LEFT([@Comment],4)="Sick",LEFT([@Comment],4)="FoMa"),1,""))	=(IF(LEFT([@Comment],4)="Full",1,IF(LEFT([@Comment],4)="Half",0.5,"")))	=(IF(LEFT([@Comment],5)="BkHol",1,""))
2025_2_4	2	January	2025	Test Employee	09/01/2025	Thursday	5	8:30	17:00	=IF(AND([@[Time In]]="",[@[Time Out]]=""),"",(IF((IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-0.5)>8,8,(IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-0.5))))	=IF((IF([@[Time In]]="","",(IF([@[Regular Hours]]<8,0,(IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-8.5)))))<0,0,(IF([@[Time In]]="","",(IF([@[Regular Hours]]<8,0,(IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-8.5))))))	=IF(OR([@[Sick Days / Force Major]]=1,[@[Personal Holiday]]=1,[@[Public Holiday]]=1),8,IF(OR([@[Sick Days / Force Major]]=0.5,[@[Personal Holiday]]=0.5,[@[Public Holiday]]=0.5),4,(IF([@[Time In]]="","",(SUM([@[Regular Hours]],[@[Overtime Hours]]))))))	=(IF(OR(LEFT([@Comment],4)="Sick",LEFT([@Comment],4)="FoMa"),1,""))	=(IF(LEFT([@Comment],4)="Full",1,IF(LEFT([@Comment],4)="Half",0.5,"")))	=(IF(LEFT([@Comment],5)="BkHol",1,""))
2025_2_5	2	January	2025	Test Employee	10/01/2025	Friday	6	8:30	17:00	=IF(AND([@[Time In]]="",[@[Time Out]]=""),"",(IF((IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-0.5)>8,8,(IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-0.5))))	=IF((IF([@[Time In]]="","",(IF([@[Regular Hours]]<8,0,(IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-8.5)))))<0,0,(IF([@[Time In]]="","",(IF([@[Regular Hours]]<8,0,(IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-8.5))))))	=IF(OR([@[Sick Days / Force Major]]=1,[@[Personal Holiday]]=1,[@[Public Holiday]]=1),8,IF(OR([@[Sick Days / Force Major]]=0.5,[@[Personal Holiday]]=0.5,[@[Public Holiday]]=0.5),4,(IF([@[Time In]]="","",(SUM([@[Regular Hours]],[@[Overtime Hours]]))))))	=(IF(OR(LEFT([@Comment],4)="Sick",LEFT([@Comment],4)="FoMa"),1,""))	=(IF(LEFT([@Comment],4)="Full",1,IF(LEFT([@Comment],4)="Half",0.5,"")))	=(IF(LEFT([@Comment],5)="BkHol",1,""))
2025_2_6	2	January	2025	Test Employee	11/01/2025	Saturday	7	8:30	15:45	=IF(AND([@[Time In]]="",[@[Time Out]]=""),"",(IF((IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-0.5)>8,8,(IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-0.5))))	=IF((IF([@[Time In]]="","",(IF([@[Regular Hours]]<8,0,(IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-8.5)))))<0,0,(IF([@[Time In]]="","",(IF([@[Regular Hours]]<8,0,(IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-8.5))))))	=IF(OR([@[Sick Days / Force Major]]=1,[@[Personal Holiday]]=1,[@[Public Holiday]]=1),8,IF(OR([@[Sick Days / Force Major]]=0.5,[@[Personal Holiday]]=0.5,[@[Public Holiday]]=0.5),4,(IF([@[Time In]]="","",(SUM([@[Regular Hours]],[@[Overtime Hours]]))))))	=(IF(OR(LEFT([@Comment],4)="Sick",LEFT([@Comment],4)="FoMa"),1,""))	=(IF(LEFT([@Comment],4)="Full",1,IF(LEFT([@Comment],4)="Half",0.5,"")))	=(IF(LEFT([@Comment],5)="BkHol",1,""))
2025_2_7	2	January	2025	Test Employee	12/01/2025	Sunday	1			=IF(AND([@[Time In]]="",[@[Time Out]]=""),"",(IF((IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-0.5)>8,8,(IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-0.5))))	=IF((IF([@[Time In]]="","",(IF([@[Regular Hours]]<8,0,(IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-8.5)))))<0,0,(IF([@[Time In]]="","",(IF([@[Regular Hours]]<8,0,(IFERROR(IF(COUNT(TimesheetTestEmployee[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)-8.5))))))	=IF(OR([@[Sick Days / Force Major]]=1,[@[Personal Holiday]]=1,[@[Public Holiday]]=1),8,IF(OR([@[Sick Days / Force Major]]=0.5,[@[Personal Holiday]]=0.5,[@[Public Holiday]]=0.5),4,(IF([@[Time In]]="","",(SUM([@[Regular Hours]],[@[Overtime Hours]]))))))	=(IF(OR(LEFT([@Comment],4)="Sick",LEFT([@Comment],4)="FoMa"),1,""))	=(IF(LEFT([@Comment],4)="Full",1,IF(LEFT([@Comment],4)="Half",0.5,"")))	=(IF(LEFT([@Comment],5)="BkHol",1,""))

• excel
• excel-formula
• dynamic

Share Improve this question Follow edited Mar 3 at 15:44 John Hendrick asked Mar 2 at 15:23 John HendrickJohn Hendrick 1133 bronze badges 8

• 2 Please post sample data ( tablesgenerator/markdown_tables#google_vignette ) not an image. Make the data reproducible to your problem. Your problem includes bank/personal holidays, your image doesn't show how. Please also have expected results with given data. Right now we don't know how you want the result or how the data looks like in different scenarios – P.b Commented Mar 2 at 18:25
• Are Sat&Sun always non-working? Based on your explanation there is no overtime in your sample data; is this correct? what is the Week number? does it restart in Feb (how do you handle the partial week at the end/start of the year)? – tinazmu Commented Mar 2 at 22:45
• Saturday and sunday are can be worked, as per the updated data, and so should be taken into consideration. Sheet has now been updated ton include overtime. The Week Number relates to the actual week of the year, and continues to 52/53. Partial Week at start and end are usually non working weeks. Thank you for your help. – John Hendrick Commented Mar 3 at 0:15
• Which part is your desired output? – Harun24hr Commented Mar 3 at 2:21
• Formulas should now be showing The desired output is to show the correct Overtime amount. – John Hendrick Commented Mar 3 at 15:45

 |  Show 3 more comments

2 Answers 2

Sorted by: Reset to default 0

Paste this in a new sheet:

Index	Week Number	Month	Year	Employee Name	Date	Day	Week Day Number	Time In	Time Out	Regular Hours	Overtime Hours	Total Hours	Sick days / Force Major	Personal Holiday	Public Holiday	Job Name	Comment	Total week hour	Total week overtime
2025_1_3	1	January	2025	Test Employee	01/01/2025	Wednesday	4					8			1			=IF(AND(H2<>2,IFERROR(SUM(#REF!),0)<>28),"Incomplete week",IF(H2=2,IF(SUM(H2:H8)=28,SUM(M1:M7)+COUNTA(N1:P7)*8,IF(S1="Incomplete week","Incomplete week","")),""))	=IF(S2="Incomplete week","Incomplete week",IF(S2<>"",SUM(L2:L8),""))
2025_1_4	1	January	2025	Test Employee	02/01/2025	Thursday	5					8	1					=IF(AND(H3<>2,IFERROR(SUM(#REF!),0)<>28),"Incomplete week",IF(H3=2,IF(SUM(H3:H9)=28,SUM(M2:M8)+COUNTA(N2:P8)*8,IF(S2="Incomplete week","Incomplete week","")),""))	=IF(S3="Incomplete week","Incomplete week",IF(S3<>"",SUM(L3:L9),""))
2025_1_5	1	January	2025	Test Employee	03/01/2025	Friday	6					8		1				=IF(AND(H4<>2,IFERROR(SUM(#REF!),0)<>28),"Incomplete week",IF(H4=2,IF(SUM(H4:H10)=28,SUM(M3:M9)+COUNTA(N3:P9)*8,IF(S3="Incomplete week","Incomplete week","")),""))	=IF(S4="Incomplete week","Incomplete week",IF(S4<>"",SUM(L4:L10),""))
2025_1_6	1	January	2025	Test Employee	04/01/2025	Saturday	7					4		0,5				=IF(AND(H5<>2,IFERROR(SUM(#REF!),0)<>28),"Incomplete week",IF(H5=2,IF(SUM(H5:H11)=28,SUM(M4:M10)+COUNTA(N4:P10)*8,IF(S4="Incomplete week","Incomplete week","")),""))	=IF(S5="Incomplete week","Incomplete week",IF(S5<>"",SUM(L5:L11),""))
2025_1_7	1	January	2025	Test Employee	05/01/2025	Sunday	1											=IF(AND(H6<>2,IFERROR(SUM(#REF!),0)<>28),"Incomplete week",IF(H6=2,IF(SUM(H6:H12)=28,SUM(M5:M11)+COUNTA(N5:P11)*8,IF(S5="Incomplete week","Incomplete week","")),""))	=IF(S6="Incomplete week","Incomplete week",IF(S6<>"",SUM(L6:L12),""))
2025_2_1	2	January	2025	Test Employee	06/01/2025	Monday	2	08:30	18:00	8	1	9						=IF(AND(H7<>2,IFERROR(SUM(H1:H7),0)<>28),"Incomplete week",IF(H7=2,IF(SUM(H7:H13)=28,SUM(M6:M12)+COUNTA(N6:P12)*8,IF(S6="Incomplete week","Incomplete week","")),""))	=IF(S7="Incomplete week","Incomplete week",IF(S7<>"",SUM(L7:L13),""))
2025_2_2	2	January	2025	Test Employee	07/01/2025	Tuesday	3	08:30	19:00	8	2	10						=IF(AND(H8<>2,IFERROR(SUM(H2:H8),0)<>28),"Incomplete week",IF(H8=2,IF(SUM(H8:H14)=28,SUM(M7:M13)+COUNTA(N7:P13)*8,""),IF(S7="Incomplete week","Incomplete week","")))	=IF(S8="Incomplete week","Incomplete week",IF(S8<>"",SUM(L8:L14),""))
2025_2_3	2	January	2025	Test Employee	08/01/2025	Wednesday	4	08:30	17:00	8	0	8						=IF(AND(H9<>2,IFERROR(SUM(H3:H9),0)<>28),"Incomplete week",IF(H9=2,IF(SUM(H9:H15)=28,SUM(M8:M14)+COUNTA(N8:P14)*8,""),IF(S8="Incomplete week","Incomplete week","")))	=IF(S9="Incomplete week","Incomplete week",IF(S9<>"",SUM(L9:L15),""))
2025_2_4	2	January	2025	Test Employee	09/01/2025	Thursday	5	08:30	17:00	8	0	8						=IF(AND(H10<>2,IFERROR(SUM(H4:H10),0)<>28),"Incomplete week",IF(H10=2,IF(SUM(H10:H16)=28,SUM(M9:M15)+COUNTA(N9:P15)*8,""),IF(S9="Incomplete week","Incomplete week","")))	=IF(S10="Incomplete week","Incomplete week",IF(S10<>"",SUM(L10:L16),""))
2025_2_5	2	January	2025	Test Employee	10/01/2025	Friday	6	08:30	17:00	8	0	8						=IF(AND(H11<>2,IFERROR(SUM(H5:H11),0)<>28),"Incomplete week",IF(H11=2,IF(SUM(H11:H17)=28,SUM(M10:M16)+COUNTA(N10:P16)*8,""),IF(S10="Incomplete week","Incomplete week","")))	=IF(S11="Incomplete week","Incomplete week",IF(S11<>"",SUM(L11:L17),""))
2025_2_6	2	January	2025	Test Employee	11/01/2025	Saturday	7	08:30	15:45	6,75	0	6,75						=IF(AND(H12<>2,IFERROR(SUM(H6:H12),0)<>28),"Incomplete week",IF(H12=2,IF(SUM(H12:H18)=28,SUM(M11:M17)+COUNTA(N11:P17)*8,""),IF(S11="Incomplete week","Incomplete week","")))	=IF(S12="Incomplete week","Incomplete week",IF(S12<>"",SUM(L12:L18),""))
2025_2_7	2	January	2025	Test Employee	12/01/2025	Sunday	1											=IF(AND(H13<>2,IFERROR(SUM(H7:H13),0)<>28),"Incomplete week",IF(H13=2,IF(SUM(H13:H19)=28,SUM(M12:M18)+COUNTA(N12:P18)*8,""),IF(S12="Incomplete week","Incomplete week","")))	=IF(S13="Incomplete week","Incomplete week",IF(S13<>"",SUM(L13:L19),""))

I have resolved my query. What I was wanting to do was to calculate the overtime based on the Total Number of hours worked in a week, the standard hours being 40 per week and 8 per day. Overtime is only paid once both thresholds are breached. Thank you all for your help, it was veery much appreciated. Especially Black cat, as the reminder about SUM(FILTER()) function was key.

Kind Regards, John