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

Steven,

I imagine you have people coming to a building (though it could be
buses at a bus station!). They arrive and leave at different times.
Sometimes you may have several people in the building, but you are
interested in the times when the building is empty. Here's how you can
monitor this:

Copy the data into a new sheet and combine the date and time fields
together by adding them in a new column and then fixing the values.
Mark them as "In" or "Out" in another column. The original data can
then be deleted, so that you have two columns each for "In" and "Out".
Move the "Out" data to below the "In" data and then sort the two
columns using the Date_time field. Leave C1 blank and in C2 enter this
formula:

=IF(B2="In",C1+1,C1-1)

Format as number and copy this down column C by double-clicking the
fill handle. This gives the number of occupants of the building at each
change "In" or "Out". Then in D3 enter this formula:

=IF(C2=0,A3-A2,0)

Format as [h]:mm and copy down. This will show you when the building is
unoccupied and record the duration of these events. As it happens, it
gives exactly the same results as the earlier formula, but that is just
a fluke. If you want the total unoccupied time, you can use the
formula:

=SUM(D3:D1000)

(adjusting the range as appropriate) and you could put this in D1, say,
formatted as blue and bold to stand out.

Hope this helps.

Pete

Okay.

When you say "move the out data below the in data," did you mean...

in
in
in
in
out
out
out
out

or did you mean...

in
out
in
out
in
out

???

Thanx again.

"Pete_UK" wrote:

Steven,

I imagine you have people coming to a building (though it could be
buses at a bus station!). They arrive and leave at different times.
Sometimes you may have several people in the building, but you are
interested in the times when the building is empty. Here's how you can
monitor this:

Copy the data into a new sheet and combine the date and time fields
together by adding them in a new column and then fixing the values.
Mark them as "In" or "Out" in another column. The original data can
then be deleted, so that you have two columns each for "In" and "Out".
Move the "Out" data to below the "In" data and then sort the two
columns using the Date_time field. Leave C1 blank and in C2 enter this
formula:

=IF(B2="In",C1+1,C1-1)

Format as number and copy this down column C by double-clicking the
fill handle. This gives the number of occupants of the building at each
change "In" or "Out". Then in D3 enter this formula:

=IF(C2=0,A3-A2,0)

Format as [h]:mm and copy down. This will show you when the building is
unoccupied and record the duration of these events. As it happens, it
gives exactly the same results as the earlier formula, but that is just
a fluke. If you want the total unoccupied time, you can use the
formula:

=SUM(D3:D1000)

(adjusting the range as appropriate) and you could put this in D1, say,
formatted as blue and bold to stand out.

Hope this helps.

Pete

No, you will have two columns of data for "In" (the date_time and "In"
- assume A and B) and two columns of "Out" data (date_time and "Out" -
assume C and D).

Copy C2:D_whatever and move it directly underneath A_whatever, so that
you will have twice as many records but only occupying columns A and B.
Then you can sort A and B together using A as the sort field and this
will give you a true sequence of events. Something like:

Date_Time In_Out
03/01/2004 05:40 In
03/01/2004 07:15 In
03/01/2004 07:40 Out
03/01/2004 08:05 Out
03/01/2004 09:15 In
03/01/2004 10:20 Out
03/01/2004 11:09 In
03/01/2004 13:25 In

using your second set of sample data. (Note, I have used row 1 for
headings, but leave C1 blank).

You can now enter the formula in C2 and copy down, and the other
formula in D3 and copy that down. You might like to amend this to:

=IF(C2=0,A3-A2,"")

to help the dead times stand out more clearly.

Hope this helps.

Pete