Spreadsheets

Weekday name and week number from a date for spend analysis

Add weekday name and week number columns in Excel with TEXT, WEEKDAY and ISOWEEKNUM so you can chart spending by day of week or build a heat map.

Editorial illustration for Weekday name and week number from a date for spend analysis

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

ColumnFormulaFor 5 January 2024Use it for
Weekday name=TEXT(A2,"ddd")FriLabels on a chart
Weekday number=WEEKDAY(A2,2)5Sorting Monday to Sunday
ISO week number=ISOWEEKNUM(A2)1Weekly totals
Weekend flag=IF(WEEKDAY(A2,2)>5,"Weekend","Weekday")WeekdayA 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.

DateWeekdayWEEKNUM(date,2)ISOWEEKNUM(date)
1 January 2024Monday11
31 December 2024Tuesday531
1 January 2023Sunday152

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?

  1. Add the weekday number and name columns.
  2. List 1 to 7 down a small table, with names beside them.
  3. 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.
  4. 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.
SPENDIXAI JOURNAL

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

Explore your reports