Receiving an Error checking differences in time

[Lyryx]

Debrief
I'm putting together a workbook that checks the companies scheduling is in compliance with a few working standards regarding time, it seemed like an easy task at first but my formula seems to work sometimes but not others. I haven't worked with Time before in excel so it's a little unknown to me.


What I am trying to do
Create a formula that checks that there are 11 hours between the end time of the first shift and the start time of the second shift.

So if an Employee ends their Monday at 9:00pm, The earliest they should be working will be Tuesday at 8:00am.

My Code
I had thought something like this would work, but it only returns a value when the times are both "AM" or "PM"

=TEXT(E8-F7,"h:mm")

Where E8 = Start time of the next shift and F7 = End time of the current shift

Workbook Sample
*I didn't bother including the column with my formula from before as it is currently not working

[TABLE="width: 622"]
<tbody>[TR]
[TD]Employee # (C)[/TD]
[TD]Date (D)[/TD]
[TD]Start (E)[/TD]
[TD]End (F)[/TD]
[TD]Duration (G)[/TD]
[TD]Site (H)[/TD]
[/TR]
[TR]
[TD="align: right"]1204[/TD]
[TD]25-Apr-2016[/TD]
[TD="align: right"]7:15:00 AM[/TD]
[TD="align: right"]8:45:00 AM[/TD]
[TD]1:30[/TD]
[TD]CCAC[/TD]
[/TR]
[TR]
[TD="align: right"]1204[/TD]
[TD]25-Apr-2016[/TD]
[TD="align: right"]9:00:00 AM[/TD]
[TD="align: right"]12:00:00 PM[/TD]
[TD]3:00[/TD]
[TD]OUT[/TD]
[/TR]
[TR]
[TD="align: right"]1204[/TD]
[TD]27-Apr-2016[/TD]
[TD="align: right"]10:00:00 AM[/TD]
[TD="align: right"]11:00:00 AM[/TD]
[TD]1:00[/TD]
[TD]OUT[/TD]
[/TR]
[TR]
[TD="align: right"]1204[/TD]
[TD]27-Apr-2016[/TD]
[TD="align: right"]12:00:00 PM[/TD]
[TD="align: right"]1:00:00 PM[/TD]
[TD]1:00[/TD]
[TD]OUT[/TD]
[/TR]
[TR]
[TD="align: right"]1204[/TD]
[TD]28-Apr-2016[/TD]
[TD="align: right"]12:00:00 PM[/TD]
[TD="align: right"]1:00:00 PM[/TD]
[TD]1:00[/TD]
[TD]OUT[/TD]
[/TR]
[TR]
[TD="align: right"]1204[/TD]
[TD]29-Apr-2016[/TD]
[TD="align: right"]10:00:00 AM[/TD]
[TD="align: right"]11:00:00 AM[/TD]
[TD]1:00[/TD]
[TD]OUT[/TD]
[/TR]
[TR]
[TD="align: right"]1204[/TD]
[TD]29-Apr-2016[/TD]
[TD="align: right"]12:00:00 PM[/TD]
[TD="align: right"]1:00:00 PM[/TD]
[TD]1:00[/TD]
[TD]OUT[/TD]
[/TR]
[TR]
[TD="align: right"]1204[/TD]
[TD]02-May-2016[/TD]
[TD="align: right"]7:15:00 AM[/TD]
[TD="align: right"]8:45:00 AM[/TD]
[TD]1:30[/TD]
[TD]CCAC[/TD]
[/TR]
[TR]
[TD="align: right"]1204[/TD]
[TD]02-May-2016[/TD]
[TD="align: right"]9:00:00 AM[/TD]
[TD="align: right"]12:00:00 PM[/TD]
[TD]3:00[/TD]
[TD]OUT[/TD]
[/TR]
[TR]
[TD="align: right"]1204[/TD]
[TD]04-May-2016[/TD]
[TD="align: right"]10:00:00 AM[/TD]
[TD="align: right"]11:00:00 AM[/TD]
[TD]1:00[/TD]
[TD]OUT[/TD]
[/TR]
[TR]
[TD="align: right"]1204[/TD]
[TD]04-May-2016[/TD]
[TD="align: right"]12:00:00 PM[/TD]
[TD="align: right"]1:00:00 PM[/TD]
[TD]1:00[/TD]
[TD]OUT[/TD]
[/TR]
[TR]
[TD="align: right"]1204[/TD]
[TD]06-May-2016[/TD]
[TD="align: right"]10:00:00 AM[/TD]
[TD="align: right"]11:00:00 AM[/TD]
[TD]1:00[/TD]
[TD]OUT[/TD]
[/TR]
[TR]
[TD="align: right"]1204[/TD]
[TD]06-May-2016[/TD]
[TD="align: right"]12:00:00 PM[/TD]
[TD="align: right"]1:00:00 PM[/TD]
[TD]1:00[/TD]
[TD]OUT[/TD]
[/TR]
</tbody>[/TABLE]


Too Long Didn't Read
I need to write a formula that will tell me how much time is between the end time of the first shift and the start time of the next shift.


Thank you in advance

 

[FormR]

Hi, something like this maybe:

Excel 2013
	A	B	C	D	E	F	G	H
1	Employee # (C)	Date (D)	Start (E)	End (F)	Duration (G)	Site (H)
2	1204	25-Apr-16	7:15:00 AM	8:45:00 AM	01:30	CCAC
3	1204	25-Apr-16	9:00:00 AM	12:00:00 PM	03:00	OUT	00:15	< Custom Formatted as [hh]:mm
4	1204	27-Apr-16	10:00:00 AM	11:00:00 AM	01:00	OUT	46:00
5	1204	27-Apr-16	12:00:00 PM	1:00:00 PM	01:00	OUT	01:00
6	1204	28-Apr-16	12:00:00 PM	1:00:00 PM	01:00	OUT	23:00
7	1204	29-Apr-16	10:00:00 AM	11:00:00 AM	01:00	OUT	21:00
8	1204	29-Apr-16	12:00:00 PM	1:00:00 PM	01:00	OUT	01:00
9	1204	02-May-16	7:15:00 AM	8:45:00 AM	01:30	CCAC	66:15
10	1204	02-May-16	9:00:00 AM	12:00:00 PM	03:00	OUT	00:15
11	1204	04-May-16	10:00:00 AM	11:00:00 AM	01:00	OUT	46:00
12	1204	04-May-16	12:00:00 PM	1:00:00 PM	01:00	OUT	01:00
13	1204	06-May-16	10:00:00 AM	11:00:00 AM	01:00	OUT	45:00
14	1204	06-May-16	12:00:00 PM	1:00:00 PM	01:00	OUT	01:00
Sheet1

Cell Formulas
Range	Formula
G3	=(B3+C3)-(B2+D2)

 

[Lyryx]

This is definitely a step in the right direction, thank you every much!
I've attached it to an IF statement to check against the dates as well and it's exactly what I need thank you very much.


For anyone else using this as a guide, it should be noted that I needed to Reformat my cells using the formula to have it display the HH:MM instead of a decimal.


Thanks again FormR