Calculating Hours worked minus lunch if lunch taken field says yes

[nakiasl]

I'm trying to find a formula that will calculate the hours worked and only subtract 30 minutes for lunch if the column states yes lunch was take. if the column states no then should just calculate the hours worked with out 30 minutes for lunch. if this is not possible the alternative I'd be looking for is a formula that will only subtract 30 minutes for lunch if the hours worked is over 5 hours, if it is not over 5 hours then 30 minutes for lunch would not be subtracted.

I have this formula:

=IF(D5=0,,TEXT(IF(VALUE(TEXT(E5-D5,"h:mm"))>5,VALUE(TEXT(E5-D5,"h:mm"))-0.041667,(VALUE(TEXT(E5-D5,"h:mm"))-0.020833)),"h:mm"))

and it works for subtracting 30 minutes if hours worked is over 5, but if hours worked are under 5 it is till subtracting the 30 minutes.

below is what My sheet looks like. HELP PLEASE!!!

 

[Toadstool]

Hi Nakiasl,

So the Shift Hours Worked is total elapsed but Total Hours Worked (Decimal format) and Total Hours Worked (Hours format) should reduce by 30 minutes if Lunch is "Yes"?

Note the first check is if the Punch Out time is less than the Punch In time which would indicate somebody worked over midnight do the Punch Out time has 1 day added for the calculation to work correctly.

Please also note all Hour:Minute formats should use Format, Custom [hh]:mm so if any time exceeds 24 hours it calculates correctly.

Nakiasl.xlsx
	A	B	C	D	E	F	G	H	I
1	Name	Date	In Punch	Out Punch	Transfer	Lunch Yes/No	Total Hours Worked (Decimal format)	Shift Hours Worked	Total Hours Worked (Hours format)
2	NakiaSl	1/3/2022	8:30	17:55		Yes	8.92	09:25	08:55
3	NakiaSl	1/4/2022	8:45	19:00		Yes	9.75	10:15	09:45
4	NakiaSl	1/5/2022	12:15	16:30		Yes	3.75	04:15	03:45
5	NakiaSl	1/6/2022	8:30	14:40		Yes	5.67	06:10	05:40
6	NakiaSl	1/7/2022	16:15	19:00		No	2.75	02:45	02:45
7	NakiaSl	1/8/2022	8:00	11:00		No	3.00	03:00	03:00
Sheet1

Cell Formulas
Range	Formula
G2:G7	G2	=((IF($D2<$C2,($B2+1+$D2)-($B2+$C2),($B2+$D2)-($B2+$C2)))-(IF($F2="Yes",TIME(0,30,0),0)))*24
H2:H7	H2	=((IF($D2<$C2,($B2+1+$D2)-($B2+$C2),($B2+$D2)-($B2+$C2))))
I2:I7	I2	=G2/24

 

[nakiasl]

This worked perfect! Thanks so much

 

[Toadstool]

This worked perfect! Thanks so much

You're welcome.
Thanks for letting me know.