How to Create a Workday Serial Number That Resets Each Year in WPS Office Spreadsheets
Question details
The user needs a formula that creates a serial number from the two-digit year and the workday count for a date, such as 23067 for the 67th workday of 2023, skipping weekends and listed holidays.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Generating an annual workday sequence for dates in a spreadsheet so each date receives a unique number that resets on January 1.
- Observed behavior
- The desired result is a number in YYNNN format, such as 23067, based on the workday position within the year and excluding non-working days.
Verify that your date column contains valid date values and that the holiday list is stored in a single range, such as A7:A18. This ensures the NETWORKDAYS.INTL function can calculate the workday position correctly.
Create the YYNNN Serial with NETWORKDAYS.INTL
Use this formula when you need a single-cell result that combines the two-digit year with the workday count and skips weekends and holidays.
The formula uses YEAR(A3) to get the current year and subtracts 2000 to keep the two-digit year. NETWORKDAYS.INTL counts working days from January 1 of that year to the date in A3, excluding weekends with code 1 for Saturday and Sunday and any dates in the holiday range.
Enter official holiday dates as valid date values in a contiguous range, for example A7:A18, before inserting the formula.
Click the cell where the workday serial number should appear for the first date.
Type =(YEAR(A3)-2000)*1000+NETWORKDAYS.INTL(DATE(YEAR(A3),1,1),A3,1,$A$7:$A$18) and press Enter. Adjust A3 to your first date cell and $A$7:$A$18 to your holiday range.
Drag the fill handle down to apply the formula to other date rows, then select the result cells and set the number format to General if a date format appears instead of 23067.
Calculate Yearly Workday Serial Numbers in WPS Office Spreadsheets
Use WPS Office Spreadsheets to calculate a workday serial number that resets each year. The NETWORKDAYS.INTL function excludes weekends and your holiday list, and the formula adds the current two-digit year as a prefix.
- 1. Open WPS Office Spreadsheets: Launch WPS Office, click Spreadsheets, and open the workbook that contains your date and holiday data.
- 2. Enter the workday serial formula: In the first result cell, type =(YEAR(A3)-2000)*1000+NETWORKDAYS.INTL(DATE(YEAR(A3),1,1),A3,1,$A$7:$A$18) and press Enter.
- 3. Apply the formula down the column: Select the formula cell and drag the fill handle down to calculate serial numbers for all dates.
- 4. Save in Excel-compatible format: Click Menu > Save As and choose .xlsx to keep the formulas compatible with Microsoft Excel.
Frequently Asked Questions
How do I exclude custom weekend days in the workday serial formula?
Replace the third argument of NETWORKDAYS.INTL, which is 1 for Saturday/Sunday weekend, with another weekend code or a seven-character string that specifies workdays and non-workdays, such as 11 for Sunday only.
Why does the formula return 23000 or a date instead of 23001?
If January 1 is a weekend or holiday, NETWORKDAYS.INTL does not count it as a workday, so the workday count may start later. If the result looks like a date, select the cell and change the number format to General.
Can I apply this formula to a whole column in WPS Spreadsheets?
Yes. Enter the formula in the first result cell, press Enter, and then drag the fill handle down. Make sure the holiday range uses absolute references such as $A$7:$A$18.
Do I need to change the formula each year?
No. The formula uses DATE(YEAR(A3),1,1), so the year prefix and workday count reset automatically for each date. You only need to update the holiday range for the new year.
Is this formula compatible with Microsoft Excel?
Yes. NETWORKDAYS.INTL is supported in Microsoft Excel 2010 and later, and WPS Office Spreadsheets can save the workbook as an .xlsx file while keeping the formula intact.
