'%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 Generate Monday-to-Thursday Dates in Excel Using Formulas
Question details
The user needs an Excel formula to create a continuous study schedule that exclusively outputs dates from Monday to Thursday, while preventing duplicate dates when transitioning between different modules.
- Product
- Excel 365
- Device & OS
- not provided
- Scenario
- Creating a continuous study schedule timeline where tasks or modules span multiple days, but only occur from Monday to Thursday.
- Observed behavior
- The user requires a specific sequence that skips Fridays, Saturdays, and Sundays, and ensures that when a new module starts, it does not repeat a date that was already used in the previous module.
Ensure your starting date cells are explicitly formatted as 'Date' rather than 'Text' so the WORKDAY.INTL function can correctly calculate the serial values.
Use WORKDAY.INTL with a Custom Weekend Pattern
By using the WORKDAY.INTL function and specifying a 7-digit weekend string, you can define exactly which days are skipped in your schedule sequence.
The WORKDAY.INTL function evaluates dates while skipping specified non-working days. The weekend parameter '0000111' instructs Excel to treat Monday through Thursday as workdays (0) and Friday, Saturday, and Sunday as weekends (1).
Assuming your first module date reference is in cell A2, click on cell B2 and enter the formula: =WORKDAY.INTL(A2-1, 1, "0000111"). This generates the first valid Monday-Thursday date.
To ensure continuous dates without duplicating the previous module's end date, click on cell B3 and enter: =MAX(WORKDAY.INTL(A3-1, 1, "0000111"), WORKDAY.INTL(B2, 1, "0000111")).
Select cell B3, click and hold the fill handle (the small square at the bottom-right corner of the cell), and drag it downward to apply the logic to the rest of your study schedule.
Manage Complex Schedules Efficiently with WPS Spreadsheet
WPS Spreadsheet fully supports the WORKDAY.INTL function, enabling you to build dynamic, continuous study or project schedules with custom working days effortlessly.
- 1. Open a new worksheet: Launch WPS Spreadsheet and create a new blank workbook to start building your schedule.
- 2. Input your initial dates: Enter your base dates or module start references in Column A and ensure they are formatted as dates.
- 3. Apply the WORKDAY.INTL formula: In Column B, input the =WORKDAY.INTL formula with the "0000111" parameter to calculate your custom Monday-Thursday sequence.
- 4. Fill the schedule downward: Use the fill handle tool to drag the formula down the column, instantly generating your duplicate-free schedule.
Frequently Asked Questions
How do I change the formula to include Fridays in the schedule?
To include Fridays, you need to modify the custom weekend string in the WORKDAY.INTL function. Change the string from "0000111" to "0000011". In this string format, 0 represents a workday and 1 represents a non-workday, ordered from Monday to Sunday.
Why is my formula returning a #VALUE! error?
A #VALUE! error typically occurs if the starting cell reference (like A2 or A3) contains text rather than a valid date or a number. Ensure the referenced cells are formatted properly as dates.
Can I also skip specific holidays using this formula sequence?
Yes. The WORKDAY.INTL function includes an optional fourth argument for holidays. You can list your holiday dates in a separate range (for example, D2:D10) and reference them in your formula like this: =WORKDAY.INTL(A2-1, 1, "0000111", D2:D10).
