WPS Spreadsheets: Identify the 13th Working Day of Each Month
Question details
The user needs to flag the 13th working day of each month in a date column.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A worksheet contains every calendar date in column A, and column B should identify the date that is the 13th Monday-to-Friday working day of its month.
- Observed behavior
- The second column needs to display “13th Working Day” only for the matching date and remain blank for all other dates.
Make sure the values in column A are recognized as valid dates rather than text. If public holidays should be excluded, enter the holiday dates in a dedicated range such as H2:H20.
Flag the 13th working day with WORKDAY and EOMONTH
Use this formula when weekends are Saturday and Sunday and only Monday-to-Friday workdays need to be counted.
EOMONTH(A2,-1) returns the last day of the previous month, and WORKDAY counts forward to the 13th weekday of the current month. The formula can then be filled down beside the full date list.
Open the worksheet in WPS Spreadsheets and select cell B2 next to the first date in A2.
In the formula bar or directly in B2, enter =IF(A2=WORKDAY(EOMONTH(A2,-1),13),"13th Working Day","") and press Enter.
Select B2, use the fill handle, or choose Home > Fill > Down to copy the formula through the rows containing dates.
If holidays in H2:H20 should not count as workdays, replace the formula with =IF(A2=WORKDAY(EOMONTH(A2,-1),13,$H$2:$H$20),"13th Working Day","").
Use a holiday list for an adjusted 13th working day
Choose this variation when public holidays must be excluded from the workday count.
Calculate and flag monthly working days in WPS Spreadsheets
WPS Spreadsheets can identify the 13th working day with WORKDAY and EOMONTH, fill the result down a date column, and exclude holidays from a specified range when needed.
- 1. Open your spreadsheet: Launch WPS Office, open WPS Spreadsheets, and load the workbook containing the date column.
- 2. Enter the formula: Select B2 and enter =IF(A2=WORKDAY(EOMONTH(A2,-1),13),"13th Working Day","").
- 3. Include holidays if required: Add holiday dates to H2:H20 and use the holiday-aware formula with $H$2:$H$20 as the third WORKDAY argument.
- 4. Fill the formula down: Drag the B2 fill handle or use Home > Fill > Down to process the remaining dates.
Frequently Asked Questions
Why does the formula use EOMONTH(A2,-1) as the starting date?
EOMONTH(A2,-1) returns the last day of the previous month. WORKDAY then counts forward from that point to locate the 13th working day of the month containing A2.
How can I exclude public holidays from the calculation?
List the holiday dates in a range such as H2:H20, then use =IF(A2=WORKDAY(EOMONTH(A2,-1),13,$H$2:$H$20),"13th Working Day","") in B2.
What happens if the date in column A is stored as text?
The comparison may not work correctly. Select column A and use Home > Number Format > Date, or convert text dates into real date values before applying the formula.
Can this formula work with a different weekend schedule?
The standard WORKDAY function excludes Saturday and Sunday. For custom weekends, use WORKDAY.INTL and specify the appropriate weekend pattern, while keeping the same IF and EOMONTH structure.
