Hours worked formula

[Cmosley]

Hi, I’m trying to maximize our schedule we making that included an operating ratio. We’d like to calculate projected hours worked instead of manually filling that in. But I’m running into issues.. we take an hour lunch if you work 8 hours or more and 30 minutes if you work less than 8 hours.

I don’t want to add additional columns regarding start/end lunches. I know you can do if/then statements..I just haven’t figured out how to format that. Suggestions?

 

[GraH]

Assuming one does not work over night.

Book1
	B	C	D
2	Start	End	Worked
3	13:13:05	20:04:09	6:21:04
4	7:54:25	12:30:09	4:05:45
5	0:49:05	18:48:50	16:59:46
6	7:14:26	9:07:26	1:23:00
7	2:06:30	14:12:34	11:06:04
8	20:42:36	23:05:19	1:52:44
9	4:59:44	21:13:23	15:13:39
10	0:14:38	22:49:36	21:34:58
11	3:51:37	17:59:45	13:08:08
12	14:02:27	19:42:40	5:10:13
Sheet1

Cell Formulas
Range	Formula
D3:D12	D3	=IF((C3-B3)>=TIME(9,0,0),(C3-B3)-TIME(1,0,0),(C3-B3)-TIME(0,30,0))