How to Treat Sunday Through Tuesday as Workdays in Excel
Question details
Configure Excel to calculate durations and dates using a non-standard workweek consisting only of Sunday, Monday, and Tuesday.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Calculating project deadlines, delivery dates, or total working days when the business or employee only operates from Sunday to Tuesday.
- Observed behavior
- Standard WEEKDAY functions return only day numbers without defining workdays, while default WORKDAY functions assume standard Monday-Friday workweeks.
Verify that your version of Excel supports the .INTL functions (such as NETWORKDAYS.INTL and WORKDAY.INTL), which are available in Excel 2010 and later, including most Excel 2019 installations.
Use .INTL Functions with a Custom Weekend Mask
Use the NETWORKDAYS.INTL or WORKDAY.INTL functions with a specialized 7-character text string that tells Excel exactly which days are workdays and which are weekends.
To set up Sunday, Monday, and Tuesday as your only working days, you must use a custom string mask. In Excel, this mask represents a 7-day week starting on Monday. A "0" means the day is a workday, and a "1" means it is a non-working day (weekend).
Because the week starts on Monday, the correct string for Sunday, Monday, and Tuesday workdays is "0011110" (Monday=0, Tuesday=0, Wednesday=1, Thursday=1, Friday=1, Saturday=1, Sunday=0).
Click on the cell where you want the calculated days or date to appear.
To find the number of workdays between two dates, type =NETWORKDAYS.INTL(start_date, end_date, "0011110") into the formula bar.
If you need to calculate a future date based on a number of working days, type =WORKDAY.INTL(start_date, days, "0011110") instead.
Press Enter to execute the formula. Excel will evaluate the date range counting only Sunday, Monday, and Tuesday as valid workdays.
Identify Specific Days Using the WEEKDAY Function
If your goal is to simply identify which day of the week a date falls on to filter or format your Sunday-Tuesday schedule, use the standard WEEKDAY function.
Easily Calculate Custom Workdays in WPS Spreadsheet
WPS Office Spreadsheet fully supports advanced date functions like WORKDAY.INTL and NETWORKDAYS.INTL. You can seamlessly manage complex custom work schedules with a familiar interface and comprehensive Microsoft Excel formula compatibility.
- 1. Open WPS Spreadsheet: Launch WPS Office and open the workbook containing your project dates and schedules.
- 2. Select the Function Wizard: Click on the target cell, navigate to the Formulas tab on the ribbon, and click Insert Function.
- 3. Insert the INTL Function: Search for and select NETWORKDAYS.INTL or WORKDAY.INTL, then input your start and end dates.
- 4. Apply the Custom Mask: In the Weekend field, type the string "0011110" and click OK to instantly calculate your Sunday-Tuesday workdays.
Frequently Asked Questions
What does the "0011110" weekend string mean in Excel?
In Excel's INTL functions, this 7-character string represents the days of the week starting from Monday. A "0" defines a working day, and a "1" defines a non-working day. Therefore, "0011110" sets Monday (0), Tuesday (0), and Sunday (0) as workdays, while Wednesday through Saturday (1) are treated as weekends.
Why is my WORKDAY.INTL function returning a #NAME? error?
The #NAME? error typically appears if you are using an older version of Excel (like Excel 2007 or earlier) that does not support the .INTL functions, or if the function name is misspelled. Make sure you are using Excel 2010 or newer, and verify your spelling.
Can I use the standard WORKDAY function for a Sunday to Tuesday schedule?
No. The standard WORKDAY and NETWORKDAYS functions are hardcoded to assume a Saturday and Sunday weekend. For any non-standard weekend schedule, you must use their .INTL counterparts (WORKDAY.INTL or NETWORKDAYS.INTL).
How do I exclude holidays when treating Sunday through Tuesday as workdays?
You can add a fourth argument to your INTL function. First, list your holiday dates in a range of cells (e.g., D2:D10). Then, append this range to your formula like this: =NETWORKDAYS.INTL(A2, B2, "0011110", D2:D10). The function will automatically subtract any holidays that fall on a Sunday, Monday, or Tuesday.
