For spend analysis, add three helper columns: =TEXT(A2,"ddd") for the weekday name, =WEEKDAY(A2,2) for a number that sorts Monday first, and =ISOWEEKNUM(A2) for the week number. They turn a plain list of dates into something you can pivot and chart.
The helper columns
| Column | Formula | For 5 January 2024 | Use it for |
|---|---|---|---|
| Weekday name | =TEXT(A2,"ddd") | Fri | Labels on a chart |
| Weekday number | =WEEKDAY(A2,2) | 5 | Sorting Monday to Sunday |
| ISO week number | =ISOWEEKNUM(A2) | 1 | Weekly totals |
| Weekend flag | =IF(WEEKDAY(A2,2)>5,"Weekend","Weekday") | Weekday | A two-way split |
TEXT gives a label, and labels sort alphabetically, so Fri comes before Mon. That is why you also want the number. The argument 2 in WEEKDAY makes Monday 1 and Sunday 7. Leave it out and Excel counts Sunday as 1, which surprises people. Use dddd instead of ddd for the full name. The names follow your system language.
Which week number should I use?
Excel has two. WEEKNUM(A2,2) numbers weeks from the week containing 1 January. ISOWEEKNUM(A2) follows the ISO rule, where week 1 is the week containing the first Thursday of the year. They agree for most of the year, and disagree around the new year.
| Date | Weekday | WEEKNUM(date,2) | ISOWEEKNUM(date) |
|---|---|---|---|
| 1 January 2024 | Monday | 1 | 1 |
| 31 December 2024 | Tuesday | 53 | 1 |
| 1 January 2023 | Sunday | 1 | 52 |
31 December 2024 falls in the week that runs from 30 December 2024 to 5 January 2025, which ISO calls week 1 of 2025. WEEKNUM calls it week 53 of 2024. A Sunday 1 January 2023 belongs to the last ISO week of 2022. Pick ISOWEEKNUM if you want consistent seven-day weeks, and keep the same one throughout.
How do I chart spending by day of week?
- Add the weekday number and name columns.
- List 1 to 7 down a small table, with names beside them.
- Total each day: =SUMIFS($C$2:$C$500,$E$2:$E$500,G2), where E is the weekday number column and G2 holds the day number.
- Insert a column chart on the totals, using the names as labels.
Say a hypothetical month has ₹2,400 on Mondays and ₹6,900 on Saturdays. The sheet will show that gap in one glance, but check the number of each weekday in the month first. A month can contain five Saturdays and four Mondays, and that alone shifts the totals. Averages per day, using COUNTIFS to count the occurrences, are fairer.
Name the helper columns clearly, such as Day, DayNo and ISOWeek, so a pivot's field list stays readable. Convert the range to a table first, and new rows will carry the formulas with them.
Can I build a calendar heat map?
Yes. Make a pivot table with the weekday number in Rows, the ISO week number in Columns and the amount in Values. Then apply Home, Conditional Formatting, Color Scales to the values. Each cell becomes a day of the week in a given week, shaded by how much was spent. It is a quick way to see which weeks and which weekdays run hot.
One more helper pays off for budgets: the week of the month. =INT((DAY(A2)-1)/7)+1 puts days 1 to 7 in week 1, days 8 to 14 in week 2 and so on, so 18 January is in week 3 because 17 divided by 7 is 2.4, which rounds down to 2, plus 1. Use it to see whether spending bunches up in the first week after a salary credit.
Before you build on it
- Confirm the dates are real numbers with =ISNUMBER(A2).
- Check a known date: 5 January 2024 should show Fri and weekday number 5.
- Remember the date used. A card purchase on Saturday that posts on Monday will move, so choose transaction date for behaviour questions.
Useful ideas start with a clearer picture of your own spending.
Explore your reports

