TIME LIMITATIONS

[cooleomter]

I have a time table about period of Work ( meaning Time Consumed for a task. My problem is that the start time and end time is only given. The break times are not deducted.

for example: start 7:00:00 am end 4:00:00 pm breaktimes are 9:00:00 am to 9:15:00 AM which is 15 minutes , 10:30:00 am to 11:30:00 AM which is 60 minutes, and 2:00:00 pm to 2:15:00 AM which is 15 minutes
total Working hours per day is 7.5 hours and total breaktime is 1.5 hours.

Problem: if there are schedules of 2:00:00 pm until 3:00;00 pm and there so many data..

Need: Formula to adjust the whole Period columns.
(End time minus Start time) minus (if end time is in between 7am to 10:29:59 then 15 minutes, if end time is in between 7am to 1:59:59 pm then 1hr and15 minutes, if end time is in between 7am to 4:00:00 pm then 1hr. 30 minutes)
Tried: =(D8-C8)-IF(OR(D8<"07:00:00 AM",D8>="09:15:00 AM"),"00:15:00","0")-IF(OR(D8<"10:30:00 AM",D8>="01:59:59 PM"),"01:00:00","0")-IF(OR(D8<"02:15:00 PM",D8>="04:00:00 PM"),"00:15:00","0")


Can you help me.. Thank you

 

[cooleomter]

FORMALUAE.xlsx
	A	B	C	D
1	Start	End	Period	minus the breaktimes
2	8:05:00 AM	10:00:00 AM	1:55:00	?
3	8:15:00 AM	10:05:00 AM	1:50:00	?
4	8:25:42 AM	1:00:37 PM	4:34:55	?
5	9:15:11 AM	10:30:48 AM	1:15:37	?
6	10:07:32 AM	3:50:52 PM	5:43:20	?
7	11:30:21 AM	1:00:35 PM	1:30:14	?
8	1:01:08 PM	3:55:00 PM	2:53:52	?
9	1:00:00 PM	4:00:00 PM	3:00:00	?
10
11
12
Sheet1

Cell Formulas
Range	Formula
C2:C9	C2	=B2-A2