Overtime hours against Basic hours

[ExcelRoy]

Hi all,

Could I please ask for some assistance please
I have the following formula which checks the hours worked in S11 and displays any hours over 7.5 (Mon-Thu) or 7.0 (Fri)

=IF(S11="","",MAX(0,IF(WEEKDAY(D11)=6,S11-7,S11-7.5)))

Now this works for weekday but if some time is worked on weekends i currently don't have a calculation

Can this be added to the formula to include weekends

Many thanks

 

[ExceLoki]

how do you want it to calculate for weekend days?

 

[ExcelRoy]

any hours from saturday and sunday to be displayed
maybe go with 7 hours each

thanks

 

[ExceLoki]

=IF(S11="","",MAX(0,IF(WEEKDAY(D11)={6,7,1},S11-7,S11-7.5)))

 

[GraH]

The weekday function return the day of the week. You have = 6, so that's friday because you did not explicitely say the week starts on Monday. Turn into

weekday(date, 2)>=5

would do the trick I guess.

 

[ExcelRoy]

Hi,

Many thanks for your help but this returns a zero

I have my total hours in cell m23 and if this is >37 then i would need a calculation of hours above the following

Mon 7.5hrs
Tues 7.5hrs
Wed 7.5hrs
Thur 7.5hrs
Fri 7.0 hrs
Sat Overtime
Sun Overtime

Hoping theres a better formula to calculate

Many thanks

 

[ExceLoki]

can you share some sample data so we can see what you're working with?

 

[ExceLoki]

...

 

[ExcelRoy]

Hi,

HRGO Calculator.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
7				Week 4
8				Timesheet Entries
9
10				Date	Job	Start	Finish	Hours				Job	Basic	Overtime	Total	Purchase Order			Basic	Value	Overtime	Value
11				23/01/2023	39711	8.00	16.00	7.50				39711	7.50	0.00	151.58	231753			7.50	151.58	0.00
12				24/01/2023	39642	8.00	16.00	7.50				39642	29.50	0.50	609.22	231757			7.50	151.58	0.00
13				25/01/2023	39642	8.00	16.00	7.50				33333	4.00	0.00	80.84				7.50	151.58	0.00
14				26/01/2023	39642	8.00	16.50	8.00											8.00	161.68	0.50
15				27/01/2023	39642	8.00	15.50	7.00											7.00	141.47	0.00
16				28/01/2023	33333	8.00	12.00	4.00											4.00	80.84	0.00
17																			0.00	0.00	0.00
18																			0.00	0.00	0.00
19																			0.00	0.00	0.00
20																			0.00	0.00	0.00
21																			0.00	0.00	0.00
22
23								41.50					41.00	0.50	841.64
24
Nickolas Marques

Cell Formulas
Range	Formula
L11	L11	=IFERROR(INDEX(E11:E21,SMALL(IF(FREQUENCY(IF(E11:E21<>"",MATCH("~"&E11:E21,E11:E21&"",0)),ROW(E11:E21)-ROW(E11)+1),ROW(E11:E21)-ROW(E11)+1),ROWS(L11:L11))),"")
M11	M11	=IF(L11="","",SUMIFS(X11:X21,E11:E21,L11))
N11:N21	N11	=IF(L11="","",SUMIFS(U11:U21,E11:E21,L11))
O11	O11	=IF(L11="","",SUMIFS(AC11:AC21,E11:E21,L11))
L12	L12	=IFERROR(INDEX(E11:E21,SMALL(IF(FREQUENCY(IF(E11:E21<>"",MATCH("~"&E11:E21,E11:E21&"",0)),ROW(E11:E21)-ROW(E11)+1),ROW(E11:E21)-ROW(E11)+1),ROWS(L11:L12))),"")
M12	M12	=IF(L12="","",SUMIFS(X11:X21,E11:E21,L12))
O12	O12	=IF(L12="","",SUMIFS(AC11:AC21,E11:E21,L12))
L13	L13	=IFERROR(INDEX(E11:E21,SMALL(IF(FREQUENCY(IF(E11:E21<>"",MATCH("~"&E11:E21,E11:E21&"",0)),ROW(E11:E21)-ROW(E11)+1),ROW(E11:E21)-ROW(E11)+1),ROWS(L11:L13))),"")
M13	M13	=IF(L13="","",SUMIFS(X11:X21,E11:E21,L13))
O13	O13	=IF(L13="","",SUMIFS(AC11:AC21,E11:E21,L13))
L14	L14	=IFERROR(INDEX(E11:E21,SMALL(IF(FREQUENCY(IF(E11:E21<>"",MATCH("~"&E11:E21,E11:E21&"",0)),ROW(E11:E21)-ROW(E11)+1),ROW(E11:E21)-ROW(E11)+1),ROWS(L11:L14))),"")
M14	M14	=IF(L14="","",SUMIFS(X11:X21,E11:E21,L14))
O14	O14	=IF(L14="","",SUMIFS(AC11:AC21,E11:E21,L14))
L15	L15	=IFERROR(INDEX(E11:E21,SMALL(IF(FREQUENCY(IF(E11:E21<>"",MATCH("~"&E11:E21,E11:E21&"",0)),ROW(E11:E21)-ROW(E11)+1),ROW(E11:E21)-ROW(E11)+1),ROWS(L11:L15))),"")
M15	M15	=IF(L15="","",SUMIFS(X11:X21,E11:E21,L15))
O15	O15	=IF(L15="","",SUMIFS(AC11:AC21,E11:E21,L15))
L16	L16	=IFERROR(INDEX(E11:E21,SMALL(IF(FREQUENCY(IF(E11:E21<>"",MATCH("~"&E11:E21,E11:E21&"",0)),ROW(E11:E21)-ROW(E11)+1),ROW(E11:E21)-ROW(E11)+1),ROWS(L11:L16))),"")
M16	M16	=IF(L16="","",SUMIFS(X11:X21,E11:E21,L16))
O16	O16	=IF(L16="","",SUMIFS(AC11:AC21,E11:E21,L16))
L17	L17	=IFERROR(INDEX(E11:E21,SMALL(IF(FREQUENCY(IF(E11:E21<>"",MATCH("~"&E11:E21,E11:E21&"",0)),ROW(E11:E21)-ROW(E11)+1),ROW(E11:E21)-ROW(E11)+1),ROWS(L11:L17))),"")
M17	M17	=IF(L17="","",SUMIFS(X11:X21,E11:E21,L17))
O17	O17	=IF(L17="","",SUMIFS(AC11:AC21,E11:E21,L17))
L18	L18	=IFERROR(INDEX(E11:E21,SMALL(IF(FREQUENCY(IF(E11:E21<>"",MATCH("~"&E11:E21,E11:E21&"",0)),ROW(E11:E21)-ROW(E11)+1),ROW(E11:E21)-ROW(E11)+1),ROWS(L11:L18))),"")
M18	M18	=IF(L18="","",SUMIFS(X11:X21,E11:E21,L18))
O18	O18	=IF(L18="","",SUMIFS(AC11:AC21,E11:E21,L18))
L19	L19	=IFERROR(INDEX(E11:E21,SMALL(IF(FREQUENCY(IF(E11:E21<>"",MATCH("~"&E11:E21,E11:E21&"",0)),ROW(E11:E21)-ROW(E11)+1),ROW(E11:E21)-ROW(E11)+1),ROWS(L11:L19))),"")
M19	M19	=IF(L19="","",SUMIFS(X11:X21,E11:E21,L19))
O19	O19	=IF(L19="","",SUMIFS(AC11:AC21,E11:E21,L19))
L20	L20	=IFERROR(INDEX(E11:E21,SMALL(IF(FREQUENCY(IF(E11:E21<>"",MATCH("~"&E11:E21,E11:E21&"",0)),ROW(E11:E21)-ROW(E11)+1),ROW(E11:E21)-ROW(E11)+1),ROWS(L11:L20))),"")
M20	M20	=IF(L20="","",SUMIFS(X11:X21,E11:E21,L20))
O20	O20	=IF(L20="","",SUMIFS(AC11:AC21,E11:E21,L20))
L21	L21	=IFERROR(INDEX(E11:E21,SMALL(IF(FREQUENCY(IF(E11:E21<>"",MATCH("~"&E11:E21,E11:E21&"",0)),ROW(E11:E21)-ROW(E11)+1),ROW(E11:E21)-ROW(E11)+1),ROWS(L11:L21))),"")
M21	M21	=IF(L21="","",SUMIFS(X11:X21,E11:E21,L21))
O21	O21	=IF(L21="","",SUMIFS(AC11:AC21,E11:E21,L21))
S11:S21	S11	=IF(AND(F11<$Y$2,G11>$Y$3),(G11-F11)-($Y$3-$Y$2),(G11-F11))
T11:T21	T11	=SUM($H$3*S11)
U11:U21	U11	=IF(S11="","",MAX(0,IF(WEEKDAY(D11,2),S11-7.5,S11-7)))
H11:H21	H11	=IF(E11="","",AD11)
H23,M23:O23	H23	=SUM(H11:H21)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[ExcelRoy]

Column U is where i need the overtime

Thanks