How to Set Excel Workdays from Sunday Through Thursday
Question details
The user needs to configure date calculation formulas so that the working week runs from Sunday through Thursday, automatically skipping Friday and Saturday as non-working days.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Developing project timelines, calculating durations, or shifting task start dates in a Gantt chart when tasks fall on a Friday or Saturday weekend.
- Observed behavior
- Standard date functions default to a Monday-to-Friday workweek, which calculates incorrect start and end dates for regions or schedules operating on a Sunday-to-Thursday week.
Ensure you know the exact start dates for your tasks and have a clear list of any regional public holidays you need to exclude from the workday count.
Use a 7-Character Weekend String Pattern
Use a custom binary string with .INTL date functions to precisely define which days of the week are workdays and which are weekends.
Excel's WORKDAY.INTL and NETWORKDAYS.INTL functions accept a 7-character string of 1s and 0s to represent Monday through Sunday. A '0' indicates a workday, while a '1' indicates a non-working day.
For a standard Sunday through Thursday workweek, Friday and Saturday are weekends. The corresponding string is "0000110". If your organization has custom non-working days (e.g., taking Wednesday through Saturday off), you can adjust the string accordingly, such as "0011110".
Click on the cell where you want the calculated task end date or workday count to appear in your Gantt chart.
To calculate a future date, type =WORKDAY.INTL(start_date, days, "0000110") and press Enter. Replace start_date with your cell reference (e.g., A2) and days with your duration.
If you need to find the number of working days between two known dates, type =NETWORKDAYS.INTL(start_date, end_date, "0000110") and press Enter.
Instead of typing the string in every formula, type your weekend string (like "0011110" or "0000110") into a dedicated settings cell, such as C15 on a Settings sheet. Update your formulas to reference this cell (e.g., =WORKDAY.INTL(A2, B2, Settings!$C$15)).
Use the Built-in Numeric Weekend Code
Excel provides predefined numeric codes for common weekend configurations, including a specific code for Friday and Saturday weekends.
Calculate Custom Workdays Easily with WPS Spreadsheet
WPS Office provides robust and fully compatible spreadsheet functions for complex date and time calculations. You can seamlessly manage Gantt charts and regional workweek variations using the exact same formulas as Microsoft Excel.
- 1. Open WPS Spreadsheet: Launch WPS Office and open your project schedule or Gantt chart workbook.
- 2. Apply the .INTL function: Select the target cell and enter =WORKDAY.INTL() or =NETWORKDAYS.INTL() based on whether you need an end date or duration.
- 3. Specify the custom weekend: Input 7 or the pattern "0000110" in the weekend argument to set Sunday through Thursday as your working days, then press Enter.
Frequently Asked Questions
How does the 7-character weekend string work in Excel?
The string consists of seven characters representing Monday through Sunday in order. A '0' means the day is a workday, and a '1' means it is a weekend or non-working day. For example, '0000110' makes Friday and Saturday non-working days.
Why does my Gantt chart task start on a Friday when it should skip to Sunday?
This happens if you are using the standard WORKDAY function, which assumes Saturday and Sunday are the only non-working days. Switch your formula to WORKDAY.INTL and specify your regional weekend pattern to correctly push start dates to Sunday.
Can I include public holidays in a custom Sunday-Thursday workweek calculation?
Yes. Both WORKDAY.INTL and NETWORKDAYS.INTL include an optional fourth argument for holidays. You can list your holiday dates in a separate cell range and add that range to your formula, for example: =WORKDAY.INTL(A2, B2, 7, Holidays!$A$2:$A$10).
