Free Practice WorkbookWhen working with dates in Excel, there’s often a need to identify weekends. Excel offers several tools and formulas to help identify weekends and work with them. In this article, I’ll walk you through what are weekend days in Excel and how you can customize them.
Key Takeaways:
- Excel treats Saturday and Sunday as weekends by default.
- You can use functions like NETWORKDAYS.INTL and WORKDAY.INTL to define non-standard weekends.
- The NETWORKDAYS function calculates weekdays between two dates, excluding weekends.
- WEEKDAY combined with conditional formatting, allows you to highlight weekends.
Table of Contents
What are the Weekend Days in Excel
By default, Excel considers Saturday and Sunday as weekend days. This is true for most regions, but I realize that not every country or business works with this standard schedule. For example, some Middle Eastern countries treat Friday and Saturday as the weekend. Excel allows you to adjust the weekend setting when you need to.
Excel’s weekend recognition comes into play when using certain date-related functions, particularly NETWORKDAYS and WORKDAY. These functions help calculate the number of working days (excluding weekends) between two dates, but more on that in a moment.
Weekends in Excel
NETWORKDAYS Function
The NETWORKDAYS function is one of the easiest ways to calculate the number of weekdays between two dates. This function automatically excludes weekends (Saturday and Sunday) unless you customise it.
=NETWORKDAYS(start_date, end_date, [holidays])
- start_date: The starting date of the period.
- end_date: The ending date of the period.
- [holidays]: An optional argument where I can list any holidays that fall between the start and end dates.
If you don’t have holidays, you can skip that last argument. So, let’s say I want to find the number of working days between January 1, 2024, and January 10, 2024. I’d type:
=NETWORKDAYS(“1/1/2024”, “1/10/2024”)
Excel will exclude the weekends (Saturday, January 6, and Sunday, January 7) and give me the number of weekdays within this period.
Customise Weekend Days
Functions like NETWORKDAYS.INTL and WORKDAY.INTL can be used to customise which days should be treated as weekends.
=NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])
The [weekend] argument allows you to specify which days are considered weekends.
- 1: Saturday and Sunday (default)
- 2: Sunday and Monday
- 3: Monday and Tuesday
- 4: Tuesday and Wednesday
- 5: Wednesday and Thursday
- 6: Thursday and Friday
- 7: Friday and Saturday
- 11: Sunday only
- 12: Monday only
- 13: Tuesday only
- 14: Wednesday only
- 15: Thursday only
- 16: Friday only
- 17: Saturday only
Let’s say I’m working in a country where the weekend falls on Wednesday and Thursday, and I want to calculate the number of weekdays between two dates.
Excel will exclude Friday and Saturday from the calculation.
WORKDAY and WORKDAY.INTL
The WORKDAY function allows me to calculate a date that is a given number of weekdays away from a start date. By default, WORKDAY excludes Saturdays and Sundays, but just like the NETWORKDAYS function, the WORKDAY.INTL version lets me adjust the weekend days.
=WORKDAY(start_date, days, [holidays])
If I wanted to find a date 5 workdays after January 1, 2024, excluding weekends and holidays, the formula will be:
This will return the date of the 5th weekday (ignoring Saturday and Sunday). If I want to adjust the weekend to Sunday and Monday, the formula will be:
Excel knows that Sunday and Monday are non-working days, so it will return a date that’s 5 working days ahead, excluding those days.
Highlight Weekend Days
Simple Formulas for Saturday and Sunday
By crafting a basic formula using the WEEKDAY function, we can check if a certain date falls on weekends.
For instance, using return_type 2 with the WEEKDAY function, we get a system where Monday is represented as “1” and Sunday as “7”. So, a formula like =WEEKDAY(Cell, 2) would reveal whether a date is a weekend by returning either “6” for Saturday or “7” for Sunday.
Use Conditional Formatting
Conditional formatting with the WEEKDAY formula allows us to visualise our weekends within a list of dates instantly.
STEP 1: Select your range of dates.
STEP 2: Go to Home tab > Conditional Formatting > New Rule.
STEP 3: Choose ‘Use a formula to determine which cells to format.’
STEP 4: Enter the formula.
STEP 5: Click on the Format button.
STEP 6: Select the desired format and click OK.
Suddenly, weekends pop out at a glance, providing an intuitive and aesthetically pleasing way to navigate through calendar data.
FAQs
How to get the weekend days in Excel?
To get the weekend days in Excel, use the WEEKDAY function in combination with an IF statement.
=IF(WEEKDAY(A2, 2)>5, “Weekend”, “Workday”)
This formula returns “Weekend” if it’s a Saturday or Sunday, else “Workday”.
How to Adjust Excel’s Weekend Parameters for Different Work Weeks?
To change weekend settings in Excel for different work weeks, use the WORKDAY.INTL function. You can set custom weekends using a 7-character code. In this code, 1 means a non-working day and 0 means a working day.
What is the default weekend in Excel?
The default weekend in Excel, when using functions like WORKDAY and NETWORKDAYS, typically includes Saturday and Sunday as non-working days.
How to include Saturday and Sunday as a WORKDAY in Excel?
To make Saturday and Sunday working days in Excel, use the WORKDAY.INTL function with a custom weekend code. For example, =WORKDAY.INTL(start_date, days, “0000010”) sets only Friday as the weekend.
John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship MyExcelOnline Academy Online Course.
30 Days - Full Access 
