Forum Discussion
Solved
Create a time slot graph
Hello! I was hoping to find some help with this issue. I manage a clinic and wanted to plot data that showed the following information:
Date
No of patients
Start time - End time.
For example,
10/18/22 - 22 patients - 6:30 am - 7:00 pm
10/19/22 - 24 patients - 6:30 am - 7:30 pm
and so on. This would be a running list.
Any help would be greatly appreciated!!
Save your workbook first, because it can be tricky to back out the following change if you don't like the results.
To change the formatting of the X (horizontal) axis, click the chart to give it focus, then click the X axis values such that you see them surrounded by a box. Right-click there to see the context menu, and click "Format Axis..." on that menu. You will get a tool section slide in from the right of the chart:
Click on the "> Labels" section to expand it, and click the radio button for "Specify interval unit" (if necessary), and change the number in the adjacent input box.
I also experimented today with user-controlled monthly and weekly summaries; see the attached workbook.
I'm unsure where your difficulty is. But if it is just in plotting...
Get your data into two columns, the date-time periods (X values) on the left, and the number of patients (Y values) on the right. Select that range (rows and columns, including column headings, if any), click the Insert menu, and then either click "Recommended charts" or a hover over a specific chart type (such as Line chart under the "Insert Line or Area Chart" icon) before you click it.
You can resize the graph, drag it to another part of the worksheet, and/or experiment with changing its properties (click on it or its different parts).
Thank you for your response. I appreciate you trying to help me out! I am trying to plot what our daily hours of operations are and denote the patient load for that day. That way, our physicians can see what the patient load is on a given day and how late we are treating. I manually enter the data in "start time", "end time" and "number of patients".
Yes, so if those data are in columns A through E, and if you can use columns F and G for the data to be plotted, the formula in column F might be:
=TEXT(A2,"mm/dd") & " " & TEXT(B2, "h:mm AM/PM") & " - " & TEXT(C2, "h:mm AM/PM")and the formula in column G simply:
=E2Copy those formulas down as needed, and you are ready to create the graph.
If you are trying to also plot "how late we are treating [patients]", which seems to be time-of-day vs. date, you can do something very similar by using two columns of 1) the date (converted to text) and 2) the end time. Here I chose a bar chart instead of a line chart:
