Averaging with Dates and Times

[lespaul]

I am importing multiple interview date and time data from LotusNotes to Excel. Once the data is there I need to determine the busiest days and time periods. Can someone give me some ideas on how I might accomplish this?

Some background information: This is an interview program used by the jail to determine if a person is eligble to be released on own recognizance (ROR). We do 40 - 70 interviews per day. The administration is threatening to cut the budget, so I have been asked to determine the busiest days and hours.

So the average interviews on all mondays, tuesdays, etc for a given time frame AND the busiest times of the day (I'm thinking about splitting by 3 hour blocks, but I'm checking on that figure).

Thanks for any insight!



Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!

 

[Zathras]

I threw together a quick and dirty test sheet that might give you some ideas (doesn't take into account multiple days):
[blue]

A1: 'Start
B1: 'Duration
C1: 'Stop
D1: 6:00:00
E1: =D1+TIMEVALUE("00:15")
       Copy E1 into F1:AA1
D2: =SUM(D3:D30)
       Copy D2 into E2:AA2
A3: =RAND()*0.65+0.25
       Copy A3 into A4:A30
B3: =RAND()*0.2+0.005
       Copy B3 into B4:B30
C3: =A3+B3
       Copy C3 into C4:C3
D3: =IF(AND(D$1>=$A3,D$1<$C3),1,0)
       Copy D3 into D3:AA30

[/color]
Select D1:AA2 and click the graphing tool. Smooth Lines on the Custom Types tab looks the best to me. After creating the graph, press F9 to recalc the random times. Then set it up with your own data.

 

[lespaul]

I'm not sure I understand what's going on!! My data looks like this

ColumnA ColumnB
5/15/2003 4:30:04 PM
5/15/2003 4:15:06 PM
5/14/2003 6:30:02 AM
5/14/2003 8:17:54 AM

So with this data I would want:

Wednesday 2
Thursday 2

and separately
Wednesday
1 - 4 PM 2

Thursday
4 - 7 PM 1
7 - 10 PM 1

Does that make sense? Is that kinda what you came up with?
Thanks!


Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!

 

[Zathras]

I was trying to take into account the length of the visits, and then taking a "snapshot" of how many visitors were on site during each window of time.

That may be over-kill if visitation times are limited to a short amount of time.

In any event, I would start with the idea of graphing the data to show how many visits are taking place during various windows of time. Whether the optimum chunk size is 3 hours or 15 minutes (or somewhere in between) is something you will have to determine.

 

[lespaul]

Thanks Zathras, but I don't have the duration information. What I have is three months worth of data. I have been able to insert a column in my data so that it's now like this:

1/1/2003 Wednesday 12:38:19 AM
1/1/2003 Wednesday 12:39:27 AM
1/1/2003 Wednesday 12:43:50 AM
1/1/2003 Wednesday 1:00:23 AM
1/1/2003 Wednesday 1:06:19 AM
1/1/2003 Wednesday 1:16:18 AM
1/1/2003 Wednesday 1:33:46 AM
1/1/2003 Wednesday 1:39:15 AM
1/1/2003 Wednesday 1:40:25 AM
1/1/2003 Wednesday 1:45:55 AM
and so on.......
3/31/2003 Monday 9:56:25 PM
3/31/2003 Monday 10:11:59 PM
3/31/2003 Monday 10:44:23 PM
3/31/2003 Monday 11:30:13 PM
3/31/2003 Monday 11:35:24 PM
3/31/2003 Monday 11:51:55 PM

I can calculate the total number of interviews performed on monday, but now I need to know how many Mondays there are between 1/1/2003 and 3/31/2003 so I can calculate the average number of interviews performed on Mondays between Jan and Mar. Again for tuesdays, etc.

So from this information I will create a chart that shows average number of interviews per day.

Then I want to be able to create 7 other charts that will show for each day of the week, the busiest average time periods. So average interviews on Monday between 12 AM - 3 AM, 3 AM - 6 AM, 6 AM - 9 AM, etc., 9 PM - 12 AM; and again for each day of the week.

Thanks for any insight!
Leslie

 

[lespaul]

So I went lo-tech (since this is a one time thing) I got out the calendar and COUNTED!!!

Still working on counting the number of interviews in each time period though!

Leslie

 

[lespaul]

So after searching the Office forum, for countif, I found one of Zathras' postings on using the database functions and went "DOH!"

So, now I am trying to create a database function that will count the number of interviews between each time period. So I have a criteria of "Monday" and now I need the criteria to find a match between the time periods. How do I set up the criteria to find a match in the range I named InterviewTime where the time is between "12:00:00 AM" - "2:59:59 AM"

Thanks!
Leslie

 

[Zathras]

Here is a variation on my earlier theme. It's just a test bench to give you an idea or two.
[blue]

D1: =TIMEVALUE("0:00")

[green] (format h:mm AM/PM)[/color]

E1: =D1+TIMEVALUE("3:00")
            Copy E1 into F1:L1
D2: =SUM(D4:D1500)
            Copy D2 into F2:K2
A3: 'Date
B3: 'Day
C3: 'Time
A4: =DATE(2003,1,1)+INT(RAND()*90)

[green] (format m/d/yyyy)[/color]

            Copy A4 into A5:A1500
B4: =TEXT(A4,"dddd")
            Copy B4 into B5:B1500
C4: =RAND()
            Copy C4 into C5:C1500
D4: =IF(AND($C4>=D$1,$C4<E$1),1,0)
            Copy D4 into D4:K1500

[/color]
Select [blue]

 D1:K2

[/color] and make a graph. (Again, I think smooth lines are best for this.) What this gives is the picture regardless of day.
Press F9 to re-randomize the data points and see a new graph.
Plug in your own data (and adjust the formulas).
Add 7 new sheets, label Sun..Sat
Sort by Day (column B) and copy/paste each day into a separate tab.
Put in formulas and a graph for each tab (day) and you're just about done.