0

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.

Improve this question
1
  • Here an idea that doesn't explain every step: Add a column for weekday (Mon - Sun). You can use the WEEKDAY function for this but it returns a number (1 - 7). Make a pivot table of your data. Make a scatter plot based on the pivot table.
    – Engineer Toast
    Jun 1, 2015 at 20:16

2 Answers 2

Reset to default
1

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).

Improve this answer
1

A simpler approach would be to select your columns and insert a pivot-chart

Click on the insert tab on the ribbon and select "Pivot Chart" under the pivot table drop down.

To graph days of the week on the Y axis, simply drag your "day" field into the row labels category and your "time" field into the values category. Excel, by default, should utilize "count" function. If not simply changing it to count will give you the number of orders that day.

To graph times of day on the Y axis, follow the above steps but interchange the variables.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.