Excel Pivot table - Summarize orders by day of the week

Question

My company makes deliveries Monday through Friday, but occasionally, we try to go the extra mile if an account needs a delivery on a Saturday or even Sunday. Recently, however we've been stretched thin, and I'm trying to get a handle on which accounts are most frequently asking this of us. I have a pivot table with standard columns: Order number, date, account name, item description, item cost, total amount

Is there a way to set up the pivot table with grouping or filters to for instance, group by day of the week (M, T, W, Th, Fr, Sat, Sun)? This way I could just group the entire year to date by which day of the week it was delivered on?

Answer 1

Is this what you are looking for? I did a pivot example with "account names" versus weekdays at which an order was placed

If so, you can do the following to your data:

1. Add a column to store the weekdays, and calculate them from your order date. Note that 1 = Sunday, 2 = Monday and so on (at least it does on my Excel version).

2. Create a Pivot table with the following fields. The pivot table will give you values from 1-7 for the weekdays, which you can then interpret as Sunday-Saturday (I added this day interpretation in Column A)