Create an Excel Chart for Patient Counts by Date and Clinic Hours
Question details
Create a chart of patient counts by date and visualize clinic operating hours.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A clinic schedule lists patient counts and start and end times for each date.
- Observed behavior
- The desired result is a readable view of patient counts over time and the operating period for each date.
Enter Date, Patient Count, Start Time, and End Time in separate columns, with one row per clinic date. Check that Excel recognizes the dates and times as values rather than text.
Create a patient count chart by date in Excel
Use a line chart to see how patient counts change across clinic dates.
Select the Date and Patient Count columns, including their headers.
Choose Insert > Recommended Charts and select a line chart, or choose Insert > Line Chart.
Right-click the horizontal axis and select Format Axis. Under Axis Options, use a date axis where available, and adjust the label interval or date units to make the dates readable.
Select the chart title and replace it with a name such as Patient Counts by Date.

Create a clinic hours timeline with a stacked bar chart
Use this separate chart to show the operating period for each date.
Chart Patient Counts and Clinic Hours in WPS Spreadsheets
Use WPS Spreadsheets to create a line chart for patient counts and a separate stacked bar timeline for clinic hours.
- 1. Open the schedule: Open WPS Spreadsheets and use Open to select your workbook. Arrange Date, Patient Count, Start Time, and End Time in separate columns.
- 2. Chart patient counts: Select the Date and Patient Count columns, then choose Insert > Chart and select a line chart.
- 3. Prepare timeline data: Add adjacent Date, Start, and Duration columns. If the original date, start, and end values are in A2, C2, and D2, enter =A2, =C2, and =MOD(D2-C2,1), then fill down.
- 4. Insert a timeline chart: Select the new timeline columns and choose Insert > Chart > Stacked Bar.
- 5. Format the timeline: Select the Start series and use Format Data Series to set its fill to No fill. Format the horizontal axis as a time and adjust its bounds to show the clinic hours.

Frequently Asked Questions
How do I make the dates on my Excel chart easier to read?
Right-click the horizontal axis and choose Format Axis. Adjust the date units or label interval under Axis Options, and resize the chart if labels still overlap.
Can one line chart show both patient counts and clinic hours?
A line chart can show counts by date, but it does not clearly represent each start-to-end period. A separate stacked bar timeline makes the operating hours easier to read.
How do I calculate a clinic period that ends after midnight?
With the start time in C2 and end time in D2, enter =MOD(D2-C2,1) in a Duration column. For a timeline extending past midnight, use an axis format such as [h]:mm and set a maximum beyond 1 day if needed.
Why does the timeline bar start at the clinic opening time?
The stacked bar uses the Start series as an offset. Set that series to No fill in Format Data Series so only the Duration portion remains visible.




