# help with formula

1. ## 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?  Register To Reply

2. ## Re: help with formula

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?
>.
>  Register To Reply

3. ## RE: help with formula

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?
>  Register To Reply

4. ## RE: help with formula

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?
> >  Register To Reply