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!!!
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!!!