Excel – Day of the week and Time Graphing in Microsoft Excel

chartsdate timemicrosoft excel

I have an Microsoft Excel document with two columns, date (mm/dd/yyyy) and time HH:MM(a/p). Each row represents an order (The day it was placed and the time it was placed).

How can I graph this so that the Y-axis is the number of orders and the X-axis is the day of the week? (Monday-Sunday)

How can I graph this so that the Y-axis is the number of orders and the X-axis is the time of day? (Hour0-Hour23)

Is this even possible using MS Excel? I've never done anything this complicated. Is it even possible to convert dates (mm/dd/yyyy) into days of the week?

I don't expect a complete answer (but if you have one please send it). I don't even know where to start or if it is even possible.

Best Answer

It is perfectly possibly to convert dates to days of week using simple formatting of the date column.

However, Do you want to sum by day of the week? If so, you need a pivot table.

The easiest way is to create a new column with the formula (assuming the date is in column A and the new column is B) =$A1 where 1 is the row number. This is easier still if you make your source data into an Excel table first. Then you only need to add the formula in the first data row (not the heading) and Excel will fill the rest for you. That also makes the next but one step easier.

Next, format your new column with a custom format of ddd which gives you a 3 letter DoW.

Now create a pivot table from the data with the DoW as the rows and the data as the sum of your order column (it may default to count rather than sum).

Related Solutions

Excel – Generate meaningful graph for times of day in Excel

Create a new column called "Time of Day" with this formula (assuming mod times start in A5):

=MROUND(A5-INT(A5),TIME(0,10,0))

(Why doesn't Excel have a FRAC function?)

This rounds the times to 10 minute intervals. You can change that to some other value (e.g. 5 or 15). Copy the formula to all rows.

Select the whole sheet and do a sort on the new column.

Now select the cells of the new column and do a subtotal, choosing "count" as the function. You can probably leave the other settings the way they are.

Now, on the left side of the sheet you'll see outline markers. Click on the second column. This should collapse the sheet to show only the counts. You can do a search and replace to make the column that the subtotal operation creates have text that you like. I replaced "Count" with nothing and it left just the times (which will become the X-axis labels).

Now select the count column and the Time of Day column, omitting the grand total at the bottom, and create a column chart.

I'm using Excel 2007, so I'm not being more detailed in my instructions since the user interface is so different from Excel 2002, but hopefully this will get you started.

alt text

Excel Line Graph with columns and rows transposed

FWIW, I laid out a data table as you described, and in Excel 2007 got the default chart to appear like you described wanting. Here's my example:

enter image description here

If that's not working, you can try this (to avoid Excel being "to smart" and applying all it's own formatting decisions).

Create a blank chart-click in a blank cell and choose insert line chart.
Right-click on chart and choose Select Data.
Add a new series.
For the label, choose your first series; for the series choose your cells B:N
In the Horizontal Axis Labels area of the dialog (right side), choose your Week Labels.
Select OK for everything and your first series should be set.
Select the rest of your series, including your series labels.
Re-select your chart and paste the values. If you Special Paste here you can select your values in rows and your first column contains series names.
Click OK to finish pasting your remaining series to the chart.

Also, you can try the Switch Row/Column button in the Select Data Dialog box.

Best Answer

Related Solutions

Excel – Generate meaningful graph for times of day in Excel

Excel Line Graph with columns and rows transposed

Related Question