Spreadsheets

Spending by day of the week in Excel with WEEKDAY

Find which day of the week your money leaves with WEEKDAY and SUMPRODUCT, from Monday to Sunday totals to a weekend versus weekday share.

Editorial illustration for Spending by day of the week in Excel with WEEKDAY

To total spending by day of the week in Excel, use WEEKDAY with return type 2, which numbers Monday as 1 and Sunday as 7: =SUMPRODUCT(--(WEEKDAY($A$2:$A$500,2)=G2),$C$2:$C$500). Put 1 to 7 in column G and fill the formula down. You can then see which day carries the most spending and how much of it falls on the weekend.

What does the formula do?

Say your log has real dates in column A and amounts in column C. WEEKDAY($A$2:$A$500,2) turns every date into a number from 1 to 7. Comparing it with G2 makes a list of TRUE and FALSE, the double minus turns that into 1 and 0, and SUMPRODUCT multiplies it by the amounts and adds. The result is the total for every Monday if G2 is 1.

Is there a simpler way without SUMPRODUCT?

Yes, a helper column. In D2 enter =TEXT(A2,"ddd") to get Mon, Tue and so on, or =WEEKDAY(A2,2) for a number, and fill down. Then each total is a plain SUMIF: =SUMIF($D$2:$D$500,"Mon",$C$2:$C$500). A helper column is easier for someone else to read, and it lets you use a pivot table. The SUMPRODUCT version avoids the extra column.

Worked example with invented totals

DayTotal (₹)
Monday3,200
Tuesday2,800
Wednesday3,100
Thursday2,900
Friday4,500
Saturday7,800
Sunday6,200

The week adds up to ₹30,500. Saturday and Sunday together are ₹14,000, and the formula for the weekend share is =SUMPRODUCT(--(WEEKDAY($A$2:$A$500,2)>5),$C$2:$C$500)/SUM($C$2:$C$500), which gives 14,000 / 30,500, about 46%. Two days of seven is about 29% of the calendar, so in this made-up month the weekend carries far more than its share of the days.

How do I get an average per Monday, not just a total?

A total is bigger on a day that occurs five times in a month, so compare averages. Put the first and last date of the period in K1 and K2. The number of Mondays between them is =NETWORKDAYS.INTL($K$1,$K$2,"0111111"). The seven-character string lists Monday to Sunday, and a 1 means a non-working day, so only Monday is counted. Change the string to "1011111" for Tuesday, and so on. Then the average spend per Monday is the Monday total divided by that count.

What should I look for in the pattern?

  • A weekend spike usually means eating out, travel and shopping. Weekdays often hide fuel, commute and lunch.
  • A bump on the first of the month or the days after salary points to bills, not habits. Separate fixed payments such as rent and EMI before reading the pattern, because they land on one date and distort one weekday.
  • Month-end Fridays may show settle-ups and cash withdrawals.

Does this work in Google Sheets?

WEEKDAY and SUMPRODUCT behave the same. Use the comma version with the amount range, as above. NETWORKDAYS.INTL is available in Sheets too.

What can I do with the answer?

Pick your biggest day and plan around it. If Saturdays are the heaviest, set a Saturday cash limit, or move shopping to a weekday. Run the same table for three months before drawing conclusions, because one month with a wedding or a festival can make a single weekday look unusual. Festival weeks, in particular, shift whole patterns around Diwali or Eid.

If you prefer a chart, select the seven totals and insert a column chart. Weekday labels read more clearly than numbers 1 to 7, so keep a label column beside the numbers and choose it as the axis. A flat chart means your spending is even across the week. A tall Saturday means that is where to look first.

Checklist

  • Real dates, tested with ISNUMBER.
  • Return type 2 so Monday is 1.
  • Fixed payments excluded or noted.
  • Three months of data before you decide anything.
SPENDIXAI JOURNAL

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

Explore your reports