Calculating average wait time across time intervals

[rjng90]

Hi I encountered a problem. I work in a clinic. Basically I have 2 columns of data. 1 column states the time of arrival of patients while the other states the respective waiting time the patients have to go through before they get to be examined by the doctor. I want to plot a Barchart/histogram that shows for every half hour interval from 8am to 5pm during which the patient arrives at the clinic, the average time they have to wait. So for example 5 patients arrive at the clinic at a time between 0800-0830, the y axis of the bar chart from 0800-0830 will show the average waiting time of these 5 patients. Can this be done please? Thank you!

 

[FDibbins]

x-posted here
Calculating average wait time across time intervals

 

[Perpa]

Hi I encountered a problem. I work in a clinic. Basically I have 2 columns of data. 1 column states the time of arrival of patients while the other states the respective waiting time the patients have to go through before they get to be examined by the doctor. I want to plot a Barchart/histogram that shows for every half hour interval from 8am to 5pm during which the patient arrives at the clinic, the average time they have to wait. So for example 5 patients arrive at the clinic at a time between 0800-0830, the y axis of the bar chart from 0800-0830 will show the average waiting time of these 5 patients. Can this be done please? Thank you!

rjng90,
I developed this for my son in law a few years ago. He wanted to do something similar to what you are trying to do.
It involves a Table with Data Validation, a Macro to extend that validation, a Pivot Table to filter the data, and a Chart to show the results.
First create a table similar to this:

Excel 2007
	A	B	C	D	E	F	G	H	I	J
1	Patient #	Ar Time	Tr Time	Wait Time (TT-AT)	Dr Time	Wait Time (DT-AT)	Doctor	Day	Date	Minutes
2	31000	7:34 AM	7:40 AM	6	7:55 AM	0:21				21
3	32000	8:00 PM	8:10 PM	10	8:23 PM	0:23			7/3/2013	23
Sheet1

Then enter the following in row 2 as indicated:

B2, C2, and E2 have Data Validation to insure correct time format is maintained 'hh:mm{space}AM'
Use these parameters: Allow: 'Time', Data: 'between', Start time: '12:00:000 AM', End Time: '11:59:00 PM'

D2=IF(OR(B2="",C2=""),"Missing Data",HOUR(C2-B2+IF(B2>C2,1))*60+MINUTE(C2-B2+IF(B2>C2,1)))

J2=IF(OR(B2="",E2=""),"Missing Data",HOUR(E2-B2+IF(B2>E2,1))*60+MINUTE(E2-B2+IF(B2>E2,1)))

Now you just copy down the Data Validation and formulae by copying the formatted cells before new data is entered. That can be automated.
I used the code below entitled 'ExtendDataValid' with an ActiveX command button.
It could very easily be something triggered when a new patient is entered in column A (Change_Event).

After data has been entered, you can create a pivot table that can be filtered on the 'Doctor', 'Patient #', 'Date', or 'Day' of the week.

Then using the pivot chart filtered data, chart the data similar to the following:
Perpa

Sub ExtendDataValid()
'
' Extend the Data Validation for another LRPlus rows
    LRwBot = Range("B65536").End(xlUp).Row
    LRPlus = LRwBot + 20                     'Number of rows to add
    
    'Arrival Time (AT)
    Range(Cells(LRwBot, 2), Cells(LRPlus, 2)).Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateTime, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="12:00:00 AM", Formula2:="11:59:00 PM"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = "hh:mm{sp}AM"
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    
    Range(Cells(LRwBot, 3), Cells(LRPlus, 3)).Select
    'Travel Time (TT)
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateTime, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="12:00:00 AM", Formula2:="11:59:00 PM"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = "hh:mm{sp}PM"
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

    Range(Cells(LRwBot, 5), Cells(LRPlus, 5)).Select
    'Doctor Time (DT)
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateTime, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="12:00:00 AM", Formula2:="11:59:00 PM"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = "hh:mm{sp}PM"
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

    Range(Cells(LRwBot, 7), Cells(LRPlus, 7)).Select
    'List of Doctors******
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=OFFSET($V$2,0,0,COUNTA(V:V)-1)"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = "Select Doctor"
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    
    Range(Cells(LRwBot, 8), Cells(LRPlus, 8)).Select
    'Days of the Week******
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=$W$2:$W$9"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = "Select DAY"
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

    Range(Cells(LRwBot, 9), Cells(LRPlus, 9)).Select
    'Date***********
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateDate, AlertStyle:=xlValidAlertStop, Operator:= _
        xlGreater, Formula1:="=TODAY()-30"     'Allows up to 30 days prior to current date
        'xlGreater, Formula1:="=TODAY()-1"     'Only allows 1 day before current date
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = "Enter Date"
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End Sub