Formula Result wrong

[Michele317]

Hello,
I post below the problem I'm having.
I have in the first 4 cells some in-out hours (D, E, H, I)
Then I have a delta. In this case, the total duration minus 5 hours. (K)
Then I have a check: if the duration is greater than 30 minutes, the result shall be the result in the row above (total duration - 5 hours). (L)

All of this works perfectly fine with many many rows but here I have a wrong result and I don't understand why and how.

Cartellino.xlsm
	D	E			H	I
30	08:49	13:57			14:26	14:53
Marzo 2025

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B3:D33	Expression	=$D3="Malattia"	text	NO
B3:D33	Expression	=$D3="Festivo"	text	NO
B3:D33	Expression	=$D3="Ferie"	text	NO
B3:D33	Expression	=$D3="Permesso"	text	NO
B3:D33	Expression	=$D3="Ferie + Permesso"	text	NO
B3:C33,H5:H9,H12:H16,H19:H23,H26:H30,H33	Expression	=$H3="Festivo"	text	NO
B3:C33,H5:H9,H12:H16,H19:H23,H26:H30,H33	Expression	=$H3="Ferie"	text	NO
B3:C33,H5:H9,H12:H16,H19:H23,H26:H30,H33	Expression	=$H3="Permesso"	text	NO
B3:C33,H5:H9,H12:H16,H19:H23,H26:H30,H33	Expression	=$H3="Ferie + Permesso"	text	NO

Cartellino.xlsm
	K	L
30	00:35	00:27
Marzo 2025

Cell Formulas
Range	Formula
K30	K30	=IF(J30="","",IF(OR(D30="Ferie",D30="Permesso",D30="Ferie + Permesso",H30="Festivo",H30="Ferie",H30="Permesso",H30="Ferie + Permesso"),IF(J30-Settings!$C$6/2<0,ROUND(J30-Settings!$C$6/2,10),J30-Settings!$C$6/2),IF(WEEKDAY(B30)=6,ROUND(J30-Settings!$C$7,10),ROUND(J30-Settings!$C$6,10))))
L30	L30	=IF(OR(K30="",D30=""),"",IF(AND(WEEKDAY(B30)=6,K30>TIME(0,30,0)),IF(E30-D30<=TIME(5,0,0),K30,I30-H30),IF(K30>TIME(0,30,0),K30,"")))

Thank you all for the help

 

[Sam_D_Ben]

I like to know what is in B30, cause could be this part giving is you the wrong result (WEEKDAY(B30)=6), i am not sure, still please check. thank you

 

[Michele317]

I like to know what is in B30, cause could be this part giving is you the wrong result (WEEKDAY(B30)=6), i am not sure, still please check. thank you

Hello Sam and thank you for the answer.
In B30 there is a simple date. In this case, as you can see below, it's a friday

Cartellino.xlsm
	B	C	D	E			H	I
30	28/03/2025	ven	08:49	13:57			14:26	14:53
Marzo 2025

Cell Formulas
Range	Formula
B30	B30	=C29+1
C30	C30	=B30

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B3:D33	Expression	=$D3="Malattia"	text	NO
B3:D33	Expression	=$D3="Festivo"	text	NO
B3:D33	Expression	=$D3="Ferie"	text	NO
B3:D33	Expression	=$D3="Permesso"	text	NO
B3:D33	Expression	=$D3="Ferie + Permesso"	text	NO
B3:C33,H5:H9,H12:H16,H19:H23,H26:H30,H33	Expression	=$H3="Festivo"	text	NO
B3:C33,H5:H9,H12:H16,H19:H23,H26:H30,H33	Expression	=$H3="Ferie"	text	NO
B3:C33,H5:H9,H12:H16,H19:H23,H26:H30,H33	Expression	=$H3="Permesso"	text	NO
B3:C33,H5:H9,H12:H16,H19:H23,H26:H30,H33	Expression	=$H3="Ferie + Permesso"	text	NO
B3:C33	Expression	=E($B3=OGGI();GIORNO.SETTIMANA($B3;2)>5)	text	NO
B3:C33	Expression	=E($B3=OGGI();GIORNO.SETTIMANA($B3;2)<=5)	text	NO

Cartellino.xlsm
	K	L
30	00:35	00:27
Marzo 2025

Cell Formulas
Range	Formula
K30	K30	=IF(J30="","",IF(OR(D30="Ferie",D30="Permesso",D30="Ferie + Permesso",H30="Festivo",H30="Ferie",H30="Permesso",H30="Ferie + Permesso"),IF(J30-Settings!$C$6/2<0,ROUND(J30-Settings!$C$6/2,10),J30-Settings!$C$6/2),IF(WEEKDAY(B30)=6,ROUND(J30-Settings!$C$7,10),ROUND(J30-Settings!$C$6,10))))
L30	L30	=IF(OR(K30="",D30=""),"",IF(AND(WEEKDAY(B30)=6,K30>TIME(0,30,0)),IF(E30-D30<=TIME(5,0,0),K30,I30-H30),IF(K30>TIME(0,30,0),K30,"")))

 

[Sam_D_Ben]

@Michele317 Please try this in L30:

=IF(OR(K30="",D30=""),"",IF(AND(WEEKDAY(B30)=7,K30>TIME(0,30,0)),IF(E30-D30<=TIME(5,0,0),K30,I30-H30),IF(K30>TIME(0,30,0),K30,"")))

 

[Michele317]

I like to know what is in B30, cause could be this part giving is you the wrong result (WEEKDAY(B30)=6), i am not sure, still please check. thank you

I have checked. Everything is corrected as it is. Thank you anyway Sam