Column showing negative or hours. Not zero.

[glenzos]

I total hours in Excel. 9am-5:30pm, half hour lunch, 8 hour day. I want the next column to show total hours, the next column, straight time (8 hours). But if I go under 8 hours, let's say 7 hours. It will read either -1, or 23. Depending on what I type. I want it to show it over 8 hours, and 0 when it's under. Can someone help me

 

[MARK858]

If I'm reading it correctly then maybe (column C formatted as hh:mm, column D formatted as General)....

Book1
	A	B	C	D
2	9:00 AM	5:30 PM	8:30	8
3	9:00 AM	4:30 PM	7:30	0
4	9:00 AM	6:30 PM	9:30	9
5	9:00 AM	7:00 PM	10:00	9.5
Sheet5

Cell Formulas
Range	Formula
C2:C5	C2	=B2-A2
D2:D5	D2	=IF(C2-TIMEVALUE("00:30:00")<TIMEVALUE("08:00:00"),0,(C2-TIMEVALUE("00:30:00"))*24)

Or (columns C and D formatted as General)

Book1
	A	B	C	D
1
2	9:00 AM	5:30 PM	8.5	8
3	9:00 AM	4:30 PM	7.5	0
4	9:00 AM	6:30 PM	9.5	9
5	9:00 AM	7:00 PM	10	9.5
Sheet5

Cell Formulas
Range	Formula
C2:C5	C2	=(B2-A2)*24
D2:D5	D2	=IF((B2-A2)*24<8.5,0,(B2-A2)*24-0.5)