help with formula
 

Hi
I have a worksheet with column C (start time) & column D (end time).
(C1:D50)
e.g
C D
07:45 08:50
11:50 12:50
In Cells A1 and B1 i have the start break time and end break time
'08:00' and '08:20' for the first break.
In Cells A2 and B2 i have the start break time and end break time
'12:00' and '12:30' for the first break.

What i want to do is calculate the amount of time lost but exclude
both break times


can anyone help?

Chip Pearson has a timehseet example with formulas on his
website:

http://www.cpearson.com/excel/overtime.htm

HTH
Jason
Atlanta, GA

-----Original Message-----
Hi
I have a worksheet with column C (start time) & column D
(end time).
(C1:D50)
e.g
C D
07:45 08:50
11:50 12:50
In Cells A1 and B1 i have the start break time and end
break time
'08:00' and '08:20' for the first break.
In Cells A2 and B2 i have the start break time and end
break time
'12:00' and '12:30' for the first break.

What i want to do is calculate the amount of time lost
but exclude
both break times


can anyone help?
.

paste this formula into cell E1

=60*24*((D1-C1)-IF(AND(D1$A$1,D1<$B$1),D1-$A$1,0)-IF(AND(C1$A$1,C1<$B$1),$B$1-C1,0))

"Kevin" wrote:

Hi
I have a worksheet with column C (start time) & column D (end time).
(C1:D50)
e.g
C D
07:45 08:50
11:50 12:50
In Cells A1 and B1 i have the start break time and end break time
'08:00' and '08:20' for the first break.
In Cells A2 and B2 i have the start break time and end break time
'12:00' and '12:30' for the first break.

What i want to do is calculate the amount of time lost but exclude
both break times


can anyone help?

Oops, I just tested that and it fails in the situation where both the start
time is before 8:00AM and the end date is after 8:20AM. Thus, we need to add
one more IF() to the formula. Try this one istead

=60*24*((D1-C1)-IF(AND(D1$A$1,D1<$B$1),D1-$A$1,0)-IF(AND(C1$A$1,C1<$B$1),$B$1-C1,0)-IF(AND(C1<$A$1,D1$B$1),$B$1-$A$1,0))


"Duke Carey" wrote:

paste this formula into cell E1

=60*24*((D1-C1)-IF(AND(D1$A$1,D1<$B$1),D1-$A$1,0)-IF(AND(C1$A$1,C1<$B$1),$B$1-C1,0))

"Kevin" wrote:

Hi
I have a worksheet with column C (start time) & column D (end time).
(C1:D50)
e.g
C D
07:45 08:50
11:50 12:50
In Cells A1 and B1 i have the start break time and end break time
'08:00' and '08:20' for the first break.
In Cells A2 and B2 i have the start break time and end break time
'12:00' and '12:30' for the first break.

What i want to do is calculate the amount of time lost but exclude
both break times


can anyone help?