Counting time instances Challenge

  • Thread starter Thread starter Nornny
  • Start date Start date
N

Nornny

This is really hard to explain, and I'm just as unclear as to what
want to do as you probably will be when you read this, but I could us
the help.

I'm given two columns that both have dates and times in each field.

A B
Date_Opened Date_Closed
1 1/1/04 7:01AM 1/2/04 4:00 PM
2 ... ...
3
4
...

All dates in the Date_Closed column is a March date. But Date_Opene
can be in January or February or March. Each row is a differen
problem. There are more columns with more fields, but assume I onl
have these two columns to work with. I need to make a function tha
counts up how many problems I have going on at a given time interva
(say 7-8AM). I want to first know the easiest way to count this an
then know the function that would do this. I can't add any new column
either. I have to extract and manipulate the data I have.

My problem comes with just realizing what I have to do. For each tim
interval, I want a count of how many problems "existed." So for 7AM,
want to know how many problems were opened, being processed, or wer
closed between 7AM and 8AM. I think I want it for any given day, but m
problem is that each day would be different, because some are open fo
a few hours and other problems are open for a few days, even weeks. Ho
would I go about counting that? Is there an easier question for me t
ask that is in essence the same? And what's the solution. I could us
your help and would be happy to clarify. :
 
Hi "Nornny >" <<[email protected]>,,

I had an extra column in my previous reply this is what it should look like:
A B C D E
Hotel-- Rooms -------Buy------------- --------Sell----------- 2004-01-02
Hotel 01 32 2004-01-01 07:01 2004-01-02 16:00 TRUE
Hotel 02 20 2004-01-02 08:59 2004-01-02 08:59 FALSE
Hotel 03 15 2004-01-02 08:00 2004-01-02 08:59 TRUE
Hotel 04 100 2004-01-02 08:01 2004-01-02 08:59 FALSE
Hotel 05 30 2004-01-02 07:59 2004-01-02 08:59 TRUE
Hotel 06 20 2004-01-02 07:59 2004-01-02 08:59 TRUE
Hotel 07 37 2004-01-02 07:59 2004-01-02 08:59 TRUE
Hotel 08 57 2004-01-02 07:59 2004-01-02 08:59 TRUE
Room Tot. 311 2004-01-02 07:59 2004-01-02 08:59 TRUE

B10: =SUBTOTAL(9,B2:OFFSET(B10,-1,0))
E2: =AND(C2<=(E$1+"08:00"), D2>=(E$1+"07:00"))
Filter on Column E

see Hotel Rooms available on a Date, Example (#hotelroom)
Summarizing Data Examples (an Overview)
http://www.mvps.org/dmcritchie/excel/sumdata.htm#hotelroom



=>
 
Thank you SO much for your help but is there any way I can do tha
without the extra True/False column? To clarify, the sheet that th
Date_Opened and Date_Closed cannot be modified. It's a sheet of dat
from which I have to extract and manipulate from in another sheet.
So say Sheet1 has:

Date_Start-------Date_closed
3/1/04 7AM 3/1/04 9PM
3/3/04 9AM 3/4/04 11AM
etc.. etc...

Sheet2 would be a field of something like this(the numbers might not b
right, but I'm trying to use the data from above to be accurate):

Time-------------Amt. of Problems(Hotel Rooms) being processed
7-8AM------------2
8-9AM------------2
9-10AM-----------3
10-11-------------3
11-12-------------3
12-1PM------------2
1-2----------------2
2-3----------------etc...


Obviously, this is a very simple example. But I know that th
Date_Closed will always end in a month in March, but not th
Date_Opened. I have to count this up using data for each month, for fo
the counts of January, it can be assumed that all date_closed will en
in January and so forth. But for simplicity's sake, I'm just trying t
figure out March for now. :) Is there any way I can combine th
function in column E
 
Hi
o.k. lets try it if you can't go with David's solution (adding an
additional column)
1. Assumptions:
- on your sheet1 you have the following columns:
-> column A: Date/Time value for openening the incident (date and
time are NOT separated in two columns)
-> column A: Date/Time value for closing the incident (date and
time are NOT separated in two columns)

- sheet2 to create the reports looks like the following:
A B C D
1 start_intvall end_intervall incident # 01-Jan-2004
2 07:00 08:00 x
3 08:00 09:00 y
.....
- I assume that the lower boundary (e.g. 07:00 in row 2) is INCLUDED
but the upper boundary EXCLUDED in the count
- You want to count for a specific date. This date is stored in cell D1
on sheet2
- Your incident closing duration can be more than 24 hours / can span
midnight

2. Formulas
Enter the following formula in C2:
=SUMPRODUCT(--('sheet1'!$A$1:$A$100<(B2+$D$1)),--('sheet1'!$B$1:$B$100<
=(A2+$D$1)))
- copy this formula down for all your rows
 
Hi "Nornny >" <<[email protected]>,

You can enter the formulas as array formulas:
See complete example in
http://www.mvps.org/dmcritchie/excel/sumdata.htm#revisited

same data as before with some additions but the formulas
would look something like the following.

Comment The following are array formulas Ctrl+Shift+Enter from formula box
you can hit F2 first so you can enter from the cell

07:00-07:59 176 B20: =SUM((C$2:C$9<=(E$1+"7:59:59")) *( D$2:D$9>=(E$1+"07:00")) * (B$2:B$9))
08:00-08:59 196 B21: =SUM((C$2:C$9<=(E$1+"8:59:59")) *( D$2:D$9>=(E$1+"08:00")) * (B$2:B$9))
09:00-09:59 167 B22: =SUM((C$2:C$9<=(E$1+"9:59:59")) *( D$2:D$9>=(E$1+"09:00")) * (B$2:B$9))
10:00-10:59 52 B23: =SUM((C$2:C$9<=(E$1+"10:59:59")) *( D$2:D$9>=(E$1+"10:00")) * (B$2:B$9))
11:00-11:59 52 B24: =SUM((C$2:C$9<=(E$1+"11:59:59")) *( D$2:D$9>=(E$1+"11:00")) * (B$2:B$9))
12:00-12:59 32 B25: =SUM((C$2:C$9<=(E$1+"12:59:59")) *( D$2:D$9>=(E$1+"12:00")) * (B$2:B$9))
13:00-13:59 32 B26: =SUM((C$2:C$9<=(E$1+"13:59:59")) *( D$2:D$9>=(E$1+"13:00")) * (B$2:B$9))
14:00-14:59 32 B27: =SUM((C$2:C$9<=(E$1+"14:59:59")) *( D$2:D$9>=(E$1+"14:00")) * (B$2:B$9))

keeping in mind that the original was to keep track of how many
hotel rooms were under ownership before, and your adaptation
might be for how many problems were outstanding?
on a certain day between certain hours or any part of that hour.

Column C has begin datetime stamps
check for truth of being less than or equal to end time check
column D has end datetime stamps
check for truth of being greater than or equal to the Start time check
cell E$1 has the date to be used
if bother are true then add 1*1*value
formulas have the time range for the date in cell E$1

I think your data appears to be one date per "record"
with the hours to match what you are looking for in
which case you would only need the starting time component
so perhaps your array formula might only look at the hour
of the starting timestamps for the entire month on the page.

09:00-11:00 115 B29: =SUM((HOUR(C$2:C$9)=9) * (B$2:B$9))

Please use your first and last name in these application newsgroups.
 
Hi Frank,
I had another go at this also (like all day). He said that he couldn't touch
the worksheet to add columns and would have to access from
another sheet (He'll have to add the sheet name infront of the
cell addresses '9004-03'!C2:C9 etc. They got dropped
in my example too long to look at (even longer than the above).

But it is hard to tell from a single row of data what the data actually
looks like. Or when the example results don't match the data.
I see now that the second example did span two dates.
But I expect he is asking for time slices of a month rather than
of a day -- and don't really know what the input is.

Anyway, I hadn't really used array formulas to the
point of really understanding them before. They are a pain in the neck
when you try to update a column of them when including the
hours in in the formulas rather than from another cell.
--
 
Wow, thanks for all your help so far, just one more problem, what if
don't know the exact size of the range? I'm assuming that there wil
never be more than 100 problems, but I don't know how many problem
there will be. When I make a range bigger than the amount of problems
there are errors in the functions. Is there any way that can be fixed
Thanks in advance
 
Btw, here's the numbers I've been getting. It seems to be off by 1 o
so, but the problem gets worse as I change the range to a size beyon
the two problems, as it counts blank cells and COUNTBLANK() doesn'
seem to be effective the way I'm doing it (just subtracting from th
SUMPRODUCT()). Thanks in advance for your help! :)


Code
-------------------

***Sheet 1***
Date_Opened Date_Closed
2/27/2004 19:01 3/1/2004 8:30
3/1/2004 8:00 3/1/2004 10:30




***Sheet 2***
(the code for C2 is
=SUMPRODUCT((data!$C$2:$C$3<(B2+$D$1))*(data!$B$2:$B$3<=(A2+$D$1))))

start end incident #
6:00 AM 7:00 AM 0
7:00 AM 8:00 AM 0
8:00 AM 9:00 AM 1
9:00 AM 10:00 AM 1
10:00 AM 11:00 AM 2
11:00 AM 12:00 AM 0

-------------------



Why am I getting those numbers so I understand the problem, becaus
Frank's function seems like it would work. But shouldn't the number
be:

1, 1, 2, 1, 1, 0 respectively
 
Hi
making the range bigger shouldn't be a problem. What error did you
exactly receive and what formula have you exactly used to produce this
error?
 
Hi David
had the same problems deriving a POSSIBLE solution which may work :-)
For time/date comparisons I try to use cells which store the conditions
as I always get the parenthesis wrong then using date/time values
hardcoded in the functions.

For time slices of a month this gets a little bit more difficult. That
was the reason I provided a day solution (little bit lazy in the
evening I was...). But it seems that we are getting closer. Lets see
what the OP want additionally
 
Hi Frank,

The only way I can solve the problem so far and that is all the more time
that I am going to spend on it for now, is to first limit the range to the
current month (this can be reworked into the end points later).

To use the difference of the non inclusive dates between, and

add 1 if the beginning date contains the any part of the time range.

add 1 if the end date contains any part of the time range.

For each record add the above together to get the number of days
for the time slice through the month.

I expect that this is a homework problem and that the extra
credit problem will be due within two weeks.

Especially by the fact that the person can not identify the
problem, can't give examples of data, and by the refusal to
use their name while expecting a lot of work to be done for them.
 
Hi David

David McRitchie wrote:
[...]
I expect that this is a homework problem and that the extra
credit problem will be due within two weeks.

Especially by the fact that the person can not identify the
problem, can't give examples of data, and by the refusal to
use their name while expecting a lot of work to be done for them.
Click to expand...

yes this thought also crossed my mind :-)
So if this is true with our starting help and your 'pseudo-code'
explanantion for getting the values for a data range he/she should be
able to work on this on her/his own

Regards
Frank
 
Hi David and Frank. I'm so sorry if I've come off as a littl
unappreciative, uncooperative, and not trying. I've honestly bee
fiddling around with this for a few days now and getting differen
results, theories, and problems each time. Unfortunately, this isn't
homework problem, which is why I'm having a hard time explaining m
difficulties with what I'm supposed to do. Whether you believe it o
not, lol, this isn't homework, but it is more or less an "assignment.
I'm self taught in Excel, and I do feel more or less a student to it a
the moment as I haven't worked with functions or anything remotel
advanced in the past. :)

I have an extract of data for each month that lists a bunch of networ
problems that were identified. Each problem is a new row, and eac
column is a different property, like when the problem was opened, whe
it was closed, where it happened, etc... From this extract, I'm tryin
to redesign an 13-month report, that manipulates and charts all th
data from each month. Unfortunately, this workbook can get quite heft
with 12 sheets of just data (one for each month), a handful of sheet
of graphs, and then a few more specialized pages. I wanted t
consolidate all the summary data of which the graphs are made into on
page. Hence the problem I arrived at when I first started this page.

You guys really have helped me get a wonderful start and great ideas a
to where I need to go. I'm forever grateful and I apologize for an
inconvience. I'm just soo confused. lol
 
Hi David
nice one :-)
I can only see one 8more academic) problem with your formulas. If both
start and end time are ending for example at 07:00 this won't be
counted

--
Regards
Frank Kabel
Frankfurt, Germany
 
It got counted but with the 6 to 7. And 7:00 to 7:01 would get
reported as 6-7 and 7-8. Best I could do.
 
Thanks for all the help, I think with shuffling things and mixing i
both your solutions around, I can get your solutions to work to m
liking. I guess at this point, I should be thankful everything is bein
counted, albeit not at the proper times, but that's okay for now. :
I'll see what I can fiddle around with, I think just copying an
pasting the Date_Entered and Date_Closed from all my data extracts t
another sheet will be the best possible solution at this point
 
Back
Top