Calculating overnight hours

[adelao747]

Hi guys,

I created a time entry spreadsheet for my company for employees who are working temporarily. However, the formula I have doesn't seem to calculate overnight hours well. The total hours keep coming-up as a negative. Does anyone know how to modify it so that I can calculate overnight hours? I have included my formula down below.

- If anyone knows how to attach a spreadsheet, please let me know, as this will be a lot easier.

Layout:
Time In (C9), Lunch Out (D9), Lunch In (E9), Time Out (F9), Total Hours (G9)

Formula:
=IF(((D9-C9)+(F9-E9))*24>8,8,((D9-C9)+(F9-E9))*24)


Thank so much!

 

[Dave Patton]

Excel 2010
	C	D	E	F	G
8	Start	Lunch		End	Total
9	18:00	20:00	20:30	4:00	9.5
10
7b

Cell Formulas
Range	Formula
G9	=(F9-C9+(F9)-(E9-D9))*24

 

[JoeMo]

Excel Workbook
	C	D	E	F	G
8	In	Lunch out	Lunch In	Out	Tot Hrs
9	7:00 AM	11:00 AM	12:00 PM	4:00 PM	8.00
10	7:00 PM	10:00 PM	11:00 PM	4:00 AM	8.00
11	7:45 PM	11:00 PM	12:00 AM	4:30 AM	7.75
Sheet14

 

[adelao747]

Thank you so much for your help!