'%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 the Second Monday in October (Columbus Day) in Excel
Question details
The user needs an Excel formula to automatically determine the exact date of the second Monday in October (Columbus Day) based on a specific year provided in a worksheet cell.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating dynamic holiday calendars, scheduling trackers, or payroll systems in Excel that require automatic holiday date calculations.
- Observed behavior
- Looking for the correct formula and syntax, particularly using functions like WORKDAY.INTL or a combination of CHOOSE and WEEKDAY, to reliably return the target date without manual calculation.
Ensure the cell where you type the target year is formatted as General or Number, and that the cell receiving the formula is formatted as a Date so the result displays correctly.
Use the WORKDAY.INTL Function (Recommended)
The most efficient way to isolate a specific day of the week within a month is by utilizing the custom weekend parameters of the WORKDAY.INTL function.
The WORKDAY.INTL function allows you to calculate dates while specifying exactly which days of the week should be treated as non-working days. By creating a custom 7-character string, we can isolate Mondays to find the exact date you need.
Type the year you want to evaluate (for example, 2024) into a blank cell, such as A1.
Select the cell where you want the Columbus Day date to appear. Type the formula: =WORKDAY.INTL(DATE(A1,10,1)+14,-1,"1011111"). If your year is stored in a different cell, like H2, change A1 to H2.
Press Enter. Right-click the result cell, choose 'Format Cells', navigate to the 'Number' tab, and select 'Date' to ensure it displays properly instead of a sequential serial number.
Use a combination of CHOOSE and WEEKDAY
An alternative method that calculates the weekday of October 1st and adds a specific number of days based on that day. Ideal for older versions of Excel.
Easily Calculate Dates and Complex Formulas with WPS Spreadsheet
WPS Office provides full support for advanced Excel functions, including WORKDAY.INTL, CHOOSE, and WEEKDAY. You can seamlessly calculate dynamic holidays like Columbus Day using exactly the same formulas, in a highly compatible and intuitive workspace.
- 1. Open WPS Spreadsheet: Launch WPS Office on your computer and open a new or existing spreadsheet document.
- 2. Input the year data: Type your target year (e.g., 2025) into a designated cell, such as A1.
- 3. Apply the calculation formula: In the result cell, paste the formula: =WORKDAY.INTL(DATE(A1,10,1)+14,-1,"1011111") and press Enter.
- 4. Format cell appropriately: Select the cell, click the 'Number Format' dropdown from the Home tab on the top ribbon, and choose 'Short Date'.
Frequently Asked Questions
Can I use this formula to calculate other holidays like Thanksgiving?
Yes. You can adapt the WORKDAY.INTL formula for other holidays. For Thanksgiving (the fourth Thursday in November), you would change the month in the DATE function to 11, adjust the string mask to target Thursdays ("1110111"), and change the days added and subtracted.
Why does my formula result show a random 5-digit number?
Spreadsheet software stores dates as sequential serial numbers for calculation purposes. If you see a 5-digit number like 45209, you simply need to change the cell's formatting to 'Date' via the Home tab or by right-clicking and selecting Format Cells.
What does the '1011111' string represent in the WORKDAY.INTL formula?
It is a 7-character string representing Monday through Sunday. A '1' denotes a non-working day (weekend), and a '0' denotes a workday. By setting Monday to '0' and all other days to '1', the formula treats only Mondays as valid workdays for its calculation.
Is the WORKDAY.INTL function available in all versions of Excel?
WORKDAY.INTL was introduced in Excel 2010. If you are using a much older version of Excel, it will not recognize this function, and you must use the alternative CHOOSE and WEEKDAY method.
