Shopping

Weekday versus weekend ordering: what a pivot table shows

Weekday vs weekend food delivery spending: group orders by day of week with a pivot table, read a made-up Friday-night peak and turn it into a weekly cap.

Editorial illustration for Weekday versus weekend ordering: what a pivot table shows

To compare weekday and weekend food delivery, add a day-of-week column to your order list, build a pivot table with the day as rows and the sum of amount as values, and compare the totals. Many people find a clear peak on Friday or Saturday evenings, and that peak is where a weekly cap works best.

Set up the data

You need a date and an amount for each delivery order, for at least four weeks. Put them in a sheet with the date in column A and the amount in column B. Then add a helper column C for the day name.

  • =TEXT(A2,"ddd") gives Mon, Tue and so on. Copy it down.
  • =WEEKDAY(A2,2) gives a number with Monday as 1 and Sunday as 7, which sorts in week order.
  • Make sure the dates are real dates and not text, otherwise both formulas give errors or wrong results.

Both work in Excel and in Google Sheets. If you use the text version, remember that alphabetical order puts Fri before Mon, so use the number column for sorting.

Build the pivot

  1. Select the data including the headers and insert a pivot table.
  2. Put the day number or name in Rows.
  3. Put Amount in Values, summarised by SUM.
  4. Add Amount again, summarised by COUNT, to see how many orders fell on each day.
  5. Sort the rows by the day number so they run Monday to Sunday.

A made-up four weeks

Say four weeks of orders add up to the following. The numbers are invented.

DayOrdersTotal over 4 weeks
Monday2₹450
Tuesday2₹380
Wednesday3₹520
Thursday3₹610
Friday5₹1,640
Saturday4₹1,380
Sunday4₹1,120

The seven days total ₹6,100. Monday to Thursday add up to ₹1,960, and Friday to Sunday add up to ₹4,140, which is about 68 per cent of the spend. Friday on its own is ₹1,640, the single heaviest day.

To get the weekend-leaning total in one cell without a pivot, with dates in A2:A500 and amounts in B2:B500, use =SUMPRODUCT((WEEKDAY($A$2:$A$500,2)>=5)*$B$2:$B$500). It adds every amount where the day is Friday, Saturday or Sunday.

What does the peak tell you?

  • Friday-night ordering is probably a habit of ending the work week, not hunger.
  • Weekday spending is already low, so cutting there saves little.
  • A cap or a plan aimed at Friday to Sunday will reach most of the money.

Turning it into a weekly cap

Average weekly spend here is ₹1,525 (6,100 divided by 4). You might set a ceiling of ₹1,300 for the week and decide in advance how to split it, for example ₹300 for Monday to Thursday and ₹1,000 across Friday to Sunday. The split matters more than the total, because it tells you in the moment that Friday has a budget.

Is it lunch or dinner?

If your order history records the time as well as the date, with both in one cell, add a column with =HOUR(A2) and pivot by hour as well. Sometimes the Friday spike is not the whole day but a single dinner. A cap aimed at Friday dinner is a sharper tool than a cap for the whole weekend, and easier to remember.

A few cautions

Four weeks is a small sample. A festival week, a trip or a guest weekend will bend the picture, so repeat the pivot over eight weeks if you can. Also check that the date in your data is the order date, since some statements show the settlement date a day or two later, which can push a Friday order into Saturday.

If you import statements into SpendixAI, delivery payments are kept with their dates and merchants and the Report builder can filter and group them, so you can export a CSV and pivot it by day in a sheet. Review rows before saving. Then check the weekend total each month against the cap you set.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports