Week numbers and weekends practice exercises
Number the weeks the way calendars in Europe do, tell weekends from weekdays and find the Monday that starts a date's week.
How to find week numbers and weekends in Excel
ISOWEEKNUM gives the ISO week number, the one used on calendars in Europe: weeks start on Monday, and week 1 is the week with the year's first Thursday. WEEKDAY turns a date into a number for the day of the week. Together they let you flag weekends and find the Monday that starts any week.
With Mar 15, 2024 in A2, =ISOWEEKNUM(A2) returns 11. =IF(WEEKDAY(A2,2)>5,"Weekend","Weekday") returns Weekday for that Friday, because WEEKDAY(A2,2) is 5 when Monday is 1. And =A2-WEEKDAY(A2,2)+1 goes back to Monday, Mar 11, 2024. Format the cell as a date to see it.
Dates around New Year can belong to a week in the other year: Dec 30, 2024 is week 1. That is normal. Plain WEEKNUM(A2) follows a different rule, where week 1 is the week with Jan 1, so use ISOWEEKNUM or WEEKNUM(A2,21) to match European calendars. The exercises below cover week numbers, telling weekend from weekday and finding the Monday of a week.
- 1Week numbers with ISOWEEKNUMIn B2, find the ISO week number of the delivery date in A2, the week number used on calendars in Europe, with ISOWEEKNUM.
- 2Weekend or weekdayPremiumIn B2, show "Weekend" if the date in A2 falls on a Saturday or Sunday and "Weekday" if not. With WEEKDAY(A2,2), Monday is 1, so the weekend is anything over 5.
- 3The Monday that starts the weekPremiumIn B2, find the Monday of the week the order date in A2 falls in. Go back as many days as WEEKDAY(A2,2) says, then forward one: A2-WEEKDAY(A2,2)+1.
Functions in this topic
Read the guide
Questions
What is the difference between WEEKNUM and ISOWEEKNUM?
WEEKNUM(A2) starts weeks on Sunday and makes the week with January 1 week 1, unless you pick another type in the second argument. ISOWEEKNUM always starts weeks on Monday and makes week 1 the one with the first Thursday. WEEKNUM(A2,21) gives the same result as ISOWEEKNUM.
How do I get a date from a week number in Excel?
Put the year in A2 and the ISO week in B2, then use =DATE(A2,1,4)-WEEKDAY(DATE(A2,1,4),2)+1+(B2-1)*7. January 4 is always in week 1, so the formula goes back to that week's Monday and adds whole weeks. The result is the Monday of the week you asked for.
How do I highlight weekends in a list of dates?
Select the dates, choose Conditional Formatting, New Rule, Use a formula, and enter =WEEKDAY($A2,2)>5, where A2 is the first date. Pick a fill color and click OK. Saturdays and Sundays get the color, and the dollar sign keeps the formula on column A as it checks each row.