'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Calculate Workdays in Excel Excluding Weekends and Holidays
Question details
The user needs to calculate a target date by adding or subtracting a specific number of working days from a start date, while accounting for custom weekend schedules and skipping specific public holidays.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Calculating accurate project deadlines, delivery dates, or historical schedules based on a non-standard workweek and recognized public holidays.
- Observed behavior
- Finding a precise target date by bypassing specified weekend days and designated holiday dates using Excel's built-in date functions.
Ensure you have all your recognized custom holiday dates entered into a column in your spreadsheet and properly formatted as dates before applying the formula.
Use the WORKDAY.INTL Function with Custom Weekend Parameters
The WORKDAY.INTL function allows you to add or subtract working days from a start date while customizing which days of the week are considered weekends and providing a specific list of holiday dates to skip.
The WORKDAY.INTL function is highly flexible because it can accept a 7-digit binary string (where '0' is a workday and '1' is a weekend day) to define custom weekend patterns starting from Monday. This is ideal for part-time schedules, shifts, or regions with non-traditional workweeks.
Click on the empty cell where you want the calculated target date to appear.
Type the formula =WORKDAY.INTL(start_date, days, [weekend], [holidays]). For example, to subtract 8 days while treating Friday, Saturday, and Sunday as weekends, type =WORKDAY.INTL(D1, -8, "0000111", Holidays).
Replace D1 with your actual start date cell. Use a positive number for future days or a negative number for past days. The string "0000111" defines Monday through Thursday as workdays.
Replace 'Holidays' with the exact range of cells containing your excluded holiday dates (e.g., F2:F10).
Press Enter to execute the formula. If the result displays as a 5-digit serial number, right-click the cell, select Format Cells, and choose Date.
Easily Calculate Work Schedules with WPS Spreadsheet
WPS Office provides a highly compatible and free Spreadsheet tool that fully supports advanced date formulas like WORKDAY and WORKDAY.INTL. You can manage project timelines and calculate deadlines effortlessly with exactly the same formulas you use in Excel.
- 1. Open your schedule document: Launch WPS Spreadsheet and open your existing project timeline or work schedule file.
- 2. Input the WORKDAY.INTL formula: Select an empty cell and type your =WORKDAY.INTL() formula just as you would in Microsoft Excel, setting your start date, days, and weekend string.
- 3. Select your holiday range: Highlight your list of holiday dates to fill in the formula's holiday argument, then press Enter to calculate your target date.
Frequently Asked Questions
What is the difference between WORKDAY and WORKDAY.INTL?
The standard WORKDAY function strictly assumes that weekends are always Saturday and Sunday. The WORKDAY.INTL function is an upgraded version that allows you to customize exactly which days of the week are considered weekends using either specific index numbers or a 7-character binary string.
Why is my WORKDAY.INTL formula returning a random 5-digit number?
Excel and WPS Spreadsheet store dates as sequential serial numbers for calculation purposes. If your formula returns a number like 44500 instead of a date, simply change the cell's number format to 'Short Date' or 'Long Date' from the Home tab.
Can I use WORKDAY.INTL to count the number of working days between two dates?
No, WORKDAY.INTL is used to find a specific target date in the future or past by adding or subtracting days. If you need to count how many working days exist between a start date and an end date, you should use the NETWORKDAYS or NETWORKDAYS.INTL function instead.
How does the weekend string like "0000111" work?
The weekend string is exactly 7 characters long, with each character representing a day of the week starting from Monday. A '0' indicates a regular workday, while a '1' indicates a non-working weekend day. Therefore, "0000111" means Monday through Thursday are workdays, and Friday through Sunday are weekends.
