Graphs comparing appointment duration.

[Driv3ra89]

Hi

I was assigned a project at my work to represent a visual graph on appointment duration. I schedule appointments for my doctor and our boss wants to see if hes completing the scheduled appointments on time. I was thinking of using a visual chart that would compare his actual scheduled appointment vs the actual time he checked them in/out. I'm thinking its possible to achieve this with a Gantt Chart ( i never created one so i'm not sure if its possible yet). Would anyone have any other recommendations?

For example:
Appointment scheduled for 9:00am - 9:40am. Actual check in/out time was 9:10-10:00am. I would want the chart to represent he started late and finished late for this appointment. I'll probably be recording his duration. I feel confident i can create a table with the necessary information, i just need a graph that can shows it.

 

[etaf]

do you want to show every appointment in a gantt chart
or would perhaps some form of % pie chart - % started late, % ontime, % finished early, % late
Average late start , average overrun
Number of appointments started late , number of appointments finished late , Number of appointments started early , Number of appointments finished early

are the appointments usually at a standard time like, 9 , 10, 11 oclock
Do they last a standard time ie 40mins

do you have a sample set of data of how you capture the appointment information

theres an interesting article on appointment times here , with graphs - not saying I can reproduce the graph, but may give you an idea of what you want to portray
https://essay.utwente.nl/57961/1/scriptie_J_Westeneng.pdf

 

[Perpa]

Driv3ra89,
I did something similar a few years back using a Pivot Table and Pivot Chart.
With data similar to this:
Excel 2007 32 bit
[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[TH][/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]D[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]E[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]F[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]G[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]H[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]I[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]J[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]K[/COLOR]​

[/TH]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​

[/TD]
[TD="bgcolor: #FFC000"]

Ar Time​

[/TD]
[TD="bgcolor: #CCFFCC"]

Tr Time​

[/TD]
[TD="bgcolor: #FFC000"]

Wait Time (TT-AT)​

[/TD]
[TD="bgcolor: #CCFFCC"]

Dr Time​

[/TD]
[TD="bgcolor: #CCFFCC"]

Wait Time (DT-AT)​

[/TD]
[TD="bgcolor: #CCFFCC"]

Day​

[/TD]
[TD="bgcolor: #CCFFCC"]

Date​

[/TD]
[TD="bgcolor: #FFC000"]

Actual Duration​

[/TD]
[TD="bgcolor: #CCFFCC"]

Ar Hour​

[/TD]
[TD="bgcolor: #FFC000"]

Scheduled Duration​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​

[/TD]
[TD]

9:11 AM​

[/TD]
[TD]

9:20 AM​

[/TD]
[TD]

9​

[/TD]
[TD]

9:30 AM​

[/TD]
[TD]

0:19​

[/TD]
[TD]Mon[/TD]
[TD]

10/17/2011​

[/TD]
[TD]

19​

[/TD]
[TD]

9:00 AM​

[/TD]
[TD]

20​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​

[/TD]
[TD]

5:09 PM​

[/TD]
[TD]

5:10 PM​

[/TD]
[TD]

1​

[/TD]
[TD]

5:10 PM​

[/TD]
[TD]

0:01​

[/TD]
[TD]Tues[/TD]
[TD]

10/18/2011​

[/TD]
[TD]

1​

[/TD]
[TD]

5:00 PM​

[/TD]
[TD]

20​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR]​

[/TD]
[TD]

3:10 PM​

[/TD]
[TD]

3:11 PM​

[/TD]
[TD]

1​

[/TD]
[TD]

3:45 PM​

[/TD]
[TD]

0:35​

[/TD]
[TD]Tues[/TD]
[TD]

10/18/2011​

[/TD]
[TD]

35​

[/TD]
[TD]

3:00 PM​

[/TD]
[TD]

20​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]5[/COLOR]​

[/TD]
[TD]

10:25 AM​

[/TD]
[TD]

10:55 AM​

[/TD]
[TD]

30​

[/TD]
[TD]

11:10 AM​

[/TD]
[TD]

0:45​

[/TD]
[TD]Sun[/TD]
[TD]

10/23/2011​

[/TD]
[TD]

45​

[/TD]
[TD]

10:00 AM​

[/TD]
[TD]

20​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]6[/COLOR]​

[/TD]
[TD]

9:45 PM​

[/TD]
[TD]

9:50 PM​

[/TD]
[TD]

5​

[/TD]
[TD]

10:10 PM​

[/TD]
[TD]

0:25​

[/TD]
[TD]Wed[/TD]
[TD]

10/26/2011​

[/TD]
[TD]

25​

[/TD]
[TD]

9:00 PM​

[/TD]
[TD]

20​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]7[/COLOR]​

[/TD]
[TD]

1:09 PM​

[/TD]
[TD]

1:12 PM​

[/TD]
[TD]

3​

[/TD]
[TD]

1:32 PM​

[/TD]
[TD]

0:23​

[/TD]
[TD]Fri[/TD]
[TD]

10/28/2011​

[/TD]
[TD]

23​

[/TD]
[TD]

1:00 PM​

[/TD]
[TD]

20​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]8[/COLOR]​

[/TD]
[TD]

12:45 PM​

[/TD]
[TD]

12:54 PM​

[/TD]
[TD]

9​

[/TD]
[TD]

2:07 PM​

[/TD]
[TD]

1:22​

[/TD]
[TD]Sun[/TD]
[TD]

10/30/2011​

[/TD]
[TD]

82​

[/TD]
[TD]

12:00 PM​

[/TD]
[TD]

20​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]9[/COLOR]​

[/TD]
[TD]

10:50 AM​

[/TD]
[TD]

10:52 AM​

[/TD]
[TD]

2​

[/TD]
[TD]

11:15 AM​

[/TD]
[TD]

0:25​

[/TD]
[TD]Tues[/TD]
[TD]

11/1/2011​

[/TD]
[TD]

25​

[/TD]
[TD]

10:00 AM​

[/TD]
[TD]

20​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]10[/COLOR]​

[/TD]
[TD]

7:33 PM​

[/TD]
[TD]

7:40 PM​

[/TD]
[TD]

7​

[/TD]
[TD]

8:22 PM​

[/TD]
[TD]

0:49​

[/TD]
[TD]Wed[/TD]
[TD]

11/2/2011​

[/TD]
[TD]

49​

[/TD]
[TD]

7:00 PM​

[/TD]
[TD]

20​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]11[/COLOR]​

[/TD]
[TD]

10:11 AM​

[/TD]
[TD]

10:20 AM​

[/TD]
[TD]

9​

[/TD]
[TD]

10:40 AM​

[/TD]
[TD]

0:29​

[/TD]
[TD]Wed[/TD]
[TD]

11/2/2011​

[/TD]
[TD]

29​

[/TD]
[TD]

10:00 AM​

[/TD]
[TD]

20​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]12[/COLOR]​

[/TD]
[TD]

12:06 PM​

[/TD]
[TD]

12:15 PM​

[/TD]
[TD]

9​

[/TD]
[TD]

12:50 PM​

[/TD]
[TD]

0:44​

[/TD]
[TD]Wed[/TD]
[TD]

11/2/2011​

[/TD]
[TD]

44​

[/TD]
[TD]

12:00 PM​

[/TD]
[TD]

20​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]13[/COLOR]​

[/TD]
[TD]

3:58 PM​

[/TD]
[TD]

3:59 PM​

[/TD]
[TD]

1​

[/TD]
[TD]

4:10 PM​

[/TD]
[TD]

0:12​

[/TD]
[TD]Fri[/TD]
[TD]

11/4/2011​

[/TD]
[TD]

12​

[/TD]
[TD]

3:00 PM​

[/TD]
[TD]

20​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]14[/COLOR]​

[/TD]
[TD]

7:49 PM​

[/TD]
[TD]

7:52 PM​

[/TD]
[TD]

3​

[/TD]
[TD]

7:58 PM​

[/TD]
[TD]

0:09​

[/TD]
[TD]Fri[/TD]
[TD]

11/4/2011​

[/TD]
[TD]

9​

[/TD]
[TD]

7:00 PM​

[/TD]
[TD]

20​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]15[/COLOR]​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

Copy these formula down:​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]16[/COLOR]​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]I2 =IF(OR(B2="",E2=""),"Missing Data",HOUR(E2-B2+IF(B2>E2,1))*60+MINUTE(E2-B2+IF(B2>E2,1)))[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]17[/COLOR]​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]J2 =FLOOR(B2, "1:00")[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: Sheet1[/TD]
[/TR]
</tbody>[/TABLE]

First I inserted a 'Pivot Chart' using the 'Insert' tab left position right next to 'Home' tab...Excel asks for the range to be included and where you want the chart to be displayed...same worksheet or another one.
Then I filled out the 'Field List' which is displayed automatically...below is a picture of the way I filled out the Field List...Notice the column header lables on the Data sheet shown in orange are the fields I selected in the Pivot Table Field List. I dragged each field to the appropriate box below...

and this is the related Pivot Table that is created...

The resulting Pivot Chart looks like this...

Perhaps the above will assist you.
Perpa