Calculate Monthly Week Numbers Starting on Saturday in WPS Office Excel
Question details
The user needs to calculate the week number within each month in a WPS spreadsheet, with Saturday as the first day of the week and the first partial week at the start of the month counted as week 1.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has dates in a worksheet and needs a formula that returns the monthly week number when weeks start on Saturday.
- Observed behavior
- The formula should reset the week count at the beginning of every month and treat the first partial Saturday-based week as week 1.
Open your workbook in WPS Spreadsheets and confirm that the dates you want to evaluate are actual date values, not text. Select the date cells and apply a short date format from Home > Number Format if you need to convert them.
Use the LET formula to calculate the Saturday-based monthly week number
Use this method for a single-cell formula that returns the week number within the month for a date in cell A1.
The formula uses LET to store the date in variable d. WEEKDAY(d,16) treats Saturday as day 1, and adding 6 before calculating the day of the month aligns the date to the Saturday-based week. The result is the week number within the month, where the first partial week is week 1.
Click the cell where you want the week number to appear, for example cell B1.
In the formula bar, type =LET(d,A1,INT((DAY(d+6-WEEKDAY(d,16))/7+1))) and press Enter.
Change the date in A1 to check that the week count resets to 1 at the start of each month and treats the first partial Saturday week as week 1.
Create a reusable LAMBDA function named MonthWk
Use this method when you need to calculate the same Saturday-based monthly week number repeatedly and want a shorter worksheet formula.
Use WPS Spreadsheets to Calculate Saturday-Based Monthly Week Numbers
WPS Office Spreadsheets supports modern formulas such as LET and LAMBDA, so you can enter the Saturday-based monthly week number formula directly in your worksheet without changing your workflow.
- 1. Download and open WPS Office: Install WPS Office and open your workbook in WPS Spreadsheets, or create a new worksheet.
- 2. Enter your date: Click cell A1 and type a date such as 2025-01-07, then press Enter.
- 3. Apply the formula: In cell B1, enter =LET(d,A1,INT((DAY(d+6-WEEKDAY(d,16))/7+1))) and press Enter to get the week number.
- 4. Fill down for other dates: Drag the fill handle from B1 down to calculate the Saturday-based monthly week number for additional dates.
Frequently Asked Questions
Why does the formula use WEEKDAY(d,16)?
WEEKDAY(d,16) returns the day of the week with Saturday as day 1. The full expression d+6-WEEKDAY(d,16) aligns the date to the Saturday-based week before extracting the day-of-month and dividing by 7.
Can I use this formula for a range of dates?
Yes. Enter the LET formula in the first result cell and use the fill handle to copy it down. The formula recalculates based on each row's date reference.
What happens if the month starts on a Saturday?
The first day is treated as the start of week 1. The formula returns 1 for that date because any partial week at the start of the month is counted as week 1.
Do I need to use both LET and LAMBDA?
No. Use LET for a one-off formula. Use LAMBDA only if you want to save the calculation as a reusable named function such as MonthWk.
Does this work in WPS Office if the workbook was created in Excel?
Yes, you can open the workbook in WPS Spreadsheets and enter or keep the formula, provided your version supports LET and LAMBDA. The file remains compatible with .xlsx format.
