How to List Weekdays in the Current Month with Excel Formulas
Question details
The user needs an Excel formula to automatically generate a continuous list of weekdays (working days) for a specified month, returning blank cells once the end of the month is reached.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating a dynamic monthly schedule, calendar, or timesheet that only includes working days and automatically adapts to the number of days in the month.
- Observed behavior
- The user wants a dynamic list of dates starting from a designated cell that populates downward, displaying only weekdays and stopping at the final day of the month.
Ensure you have the starting date of the target month entered into your reference cell (e.g., A1) and formatted properly as a Date. If you have custom holidays, prepare a list of those dates in a separate column to exclude them from your weekday list.
Combine WORKDAY.INTL and EOMONTH Formulas
Use this dynamic formula to generate consecutive working days and automatically stop outputting dates when the month ends.
The WORKDAY.INTL function calculates a future date that is a specific number of working days away from a start date, while EOMONTH identifies the last day of a given month. By wrapping these in an IF statement, you can ensure the sequence stops neatly at the end of the month.
Click on cell A1 and type the first date of the month you want to list (for example, '11/1/2023'). Ensure the cell is formatted as a Date.
Select the cell immediately below (A2) and enter the following formula: =IF(WORKDAY.INTL($A$1,ROW(1:1))>EOMONTH($A$1,0),"",WORKDAY.INTL($A$1,ROW(1:1)))
Click the small square (fill handle) in the bottom-right corner of cell A2 and drag it down the column. The formula will list consecutive weekdays and leave the remaining cells blank after the month's final weekday.
Generate Weekday Lists Effortlessly in WPS Spreadsheet
WPS Spreadsheet fully supports advanced date functions like WORKDAY.INTL and EOMONTH, allowing you to create dynamic schedules, attendance trackers, and timesheets completely free of charge.
- 1. Open a Spreadsheet: Launch WPS Office and open a new or existing workbook in the Spreadsheet module.
- 2. Enter the Reference Date: Type the first day of the desired month into cell A1.
- 3. Apply the Formula: Paste the =IF(WORKDAY.INTL...) formula into cell A2 to calculate the first sequential weekday.
- 4. Drag to Populate: Use the fill handle to drag the formula down, instantly populating your list of weekdays for the entire month.
Frequently Asked Questions
How do I exclude specific holidays using this formula?
You can list your holiday dates in a separate range of cells (e.g., D1:D5). Then, add that range as the fourth argument in both instances of the WORKDAY.INTL function in your formula, like this: WORKDAY.INTL($A$1,ROW(1:1),1,$D$1:$D$5).
Can I change the weekend days to just Sunday?
Yes. The WORKDAY.INTL function has a third argument for weekend parameters. Using '11' designates Sunday as the only weekend day. Modify the formula to: WORKDAY.INTL($A$1,ROW(1:1),11).
Why does the formula show numbers instead of dates?
Spreadsheet software stores dates as sequential serial numbers. If your cells display raw numbers (like 45200) instead of a date, simply select the cells, right-click, choose 'Format Cells', and apply a 'Short Date' or 'Long Date' format.
