Deduct 20 mins break

[Sandy42]

Is there a formula to deduct a 20 min break from hours worked when the hours are greater than 5hrs 59 mins and show the actual hours if less than 6 I.e. anything less than 6 won't have a break deducted?

 

[BadgerRoad]

I'm assuming you have a field for hours worked. How is it formatted?

 

[Sandy42]

I'm assuming you have a field for hours worked. How is it formatted?

Hi, start and finish hrs are h:mm AM/PM, total hours worked hh:mm using =mod(finish time-start time,1)

I have column hrs minus break as =if(and(isnumber(starttime),isnumber(finishtime)),mod(finishtime-starttime,1)-if(weekday(datecell,1)>0,0.33/24,1,24),0*24)

I can't figure out to only take 20 mins off anything above 5hrs 59 mins or just show hours worked if less than 6hrs?

 

[BadgerRoad]

I don't know if this is the most elegant way to do it, but it seems to work.

Book1
	A	B	C	D
1	Start time	End Time	Time worked	Time less 20 min if 6 or more hours
2	8:00:00 AM	1:00:00 PM	5:00	5:00
3	8:00:00 AM	4:00:00 PM	8:00	7:40
Sheet1

Cell Formulas
Range	Formula
C2:C3	C2	=TEXT(B2-A2,"h:mm")
D2:D3	D2	=IF(VALUE(TEXT(B2-A2,"h"))>=6, TEXT(B2-A2-1/72,"h:mm"), TEXT(B2-A2,"h:mm"))

 

[Sandy42]

I don't know if this is the most elegant way to do it, but it seems to work.

Book1
	A	B	C	D
1	Start time	End Time	Time worked	Time less 20 min if 6 or more hours
2	8:00:00 AM	1:00:00 PM	5:00	5:00
3	8:00:00 AM	4:00:00 PM	8:00	7:40
Sheet1

Cell Formulas
Range	Formula
C2:C3	C2	=TEXT(B2-A2,"h:mm")
D2:D3	D2	=IF(VALUE(TEXT(B2-A2,"h"))>=6, TEXT(B2-A2-1/72,"h:mm"), TEXT(B2-A2,"h:mm"))

Thanks for your time and input, very much appreciated.