Here's what I have...

Date_In Time_In Date_Out Time_Out
3/1 5:40 3/1 7:40
3/1 7:15 3/1 8:05
3/1 9:15 3/1 10:20
3/1 10:50 3/1 11:01
3/1 11:09 3/1 13:50
3/1 13:25 3/1 15:15

And so on, and so fourth.

What I need to accomplish is to calculate for any "dead" time between the
OUTs and the INs. On some rows, it is as simple as [=sum(d4-b5)], but I could
have many, many rows in sequence that may overlap the previous ones. This
seems so complicated to me...hope I'm explaining it correctly.

Any ideas?

Thanx.

You could enter this formula in E3:

=IF(B3D2,B3-D2,0)

Format the cell as time and then copy down column E for as many data
items as you have.

You can then sum column E in another cell and format that cell using a
custom format of [h]:mm so that it displays hours above 24.

I have assumed that the data always occurs for the same date - post
back if this is not the case and explain if 3/1 represents 3rd January
or 1st March. Is this text, or a custom date format?

Hope this helps.

Pete

This will work to check only two rows in a row, what if I have five rows in a
row one time and then only one row and then three rows?

As for the dates, it is for many dates, not just onen and the 3/1 stands for
March 1. The date is an actual date format, it only displays month and day.

Thanx. again.

"Pete_UK" wrote:

You could enter this formula in E3:

=IF(B3D2,B3-D2,0)

Format the cell as time and then copy down column E for as many data
items as you have.

You can then sum column E in another cell and format that cell using a
custom format of [h]:mm so that it displays hours above 24.

I have assumed that the data always occurs for the same date - post
back if this is not the case and explain if 3/1 represents 3rd January
or 1st March. Is this text, or a custom date format?

Hope this helps.

Pete

Steven,

I don't know what you mean by "five rows in a row". I assumed that your
posted example data was representative, and you did say that the data
was in sequence.

The formula I gave you will show any dead time between two adjacent
rows, so it will need to be copied down all the rows you have. The
total dead time is then obtained by summing all of these. Do you just
want a single-cell formula to do this?

If the dates are in Excel date format, then they can be added to the
time cells, such as:

=IF(A3+B3C2+D2,A3+B3-C2-D2,0) in E3, copied down.

I'm not sure if this is what you want, but post back if not.

Pete

Here's a good representation:

Date & Time In Date & Time Out
1 3/1/04 5:40 3/1/04 7:40
2 3/1/04 7:15 3/1/04 8:05
3 3/1/04 9:15 3/1/04 10:20
4 3/1/04 11:09 3/1/04 13:50
5 3/1/04 13:25 3/1/04 15:15
6 3/1/04 13:50 3/1/04 17:01
7 3/1/04 15:20 3/1/04 17:55
8 3/1/04 15:45 3/1/04 18:08
9 3/1/04 16:30 3/1/04 17:45
10 3/1/04 16:51 3/1/04 19:00
11 3/1/04 17:32 3/1/04 19:10
12 3/1/04 17:37 3/1/04 19:30
13 3/1/04 18:00 3/1/04 19:05
14 3/1/04 18:10 3/1/04 22:00
15 3/1/04 18:55 3/1/04 20:00
16 3/1/04 20:30 3/1/04 20:50
17 3/1/04 21:55 3/1/04 23:00
18 3/1/04 22:20 3/1/04 22:55
19 3/1/04 22:45 3/2/04 0:15
20 3/1/04 22:47 3/1/04 23:25

You'll see above, that row two starts before row one ends. The same thing
happens with rows 9 through 13 all starting before row 8 ends. Maybe that
will make a little more sense.

Thanx again.
"Pete_UK" wrote:

Steven,

I don't know what you mean by "five rows in a row". I assumed that your
posted example data was representative, and you did say that the data
was in sequence.

The formula I gave you will show any dead time between two adjacent
rows, so it will need to be copied down all the rows you have. The
total dead time is then obtained by summing all of these. Do you just
want a single-cell formula to do this?

If the dates are in Excel date format, then they can be added to the
time cells, such as:

=IF(A3+B3C2+D2,A3+B3-C2-D2,0) in E3, copied down.

I'm not sure if this is what you want, but post back if not.

Pete

Thanks for the extra data. I think you need to explain a bit more fully
what it is you want to do, and what you mean by "dead" time.

I thought you were looking for times when there was nothing "In", so
for example between rows 2 and 3 you have nothing "In" between 8:05 and
9:15, a period of 1 hour and 10 minutes, and between 3 and 4 there is
nothing "In" between 10:20 and 11:09. This is what my formula was
looking for.

On reflection, though, with this data, I think you will need a counter
which will increment with each new "In" and decrement with each "Out"
that occurs, so that at any point in time you have the number of
simultaneous "In"s. Only when this drops to 0 will you need to record
the times. Is this the case? If so, then I can propose a solution
(rather like simultaneous telephone calls!).

Please confirm that my understanding is correct.

Pete