WPS Spreadsheet Formula to Find Sunday and Saturday of a Week
Question details
The user needs formulas that return the Sunday and Saturday dates for the week containing an entered date.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- A date is entered in cell A1, and the worksheet should automatically calculate the first and last day of that week.
- Observed behavior
- The desired result is a Sunday date and a Saturday date, including weeks where the entered date is already Sunday or Saturday.
Enter a valid date in cell A1 and confirm that the cell is recognized as a date rather than plain text. Leave two nearby cells available for the Sunday and Saturday results.
Use WEEKDAY formulas to return the week’s Sunday and Saturday
Use this solution when a date in A1 should automatically produce the Sunday and Saturday dates for its week.
With WEEKDAY(A1,1), Sunday is assigned the value 1 and Saturday the value 7. Subtracting the weekday number and adding 1 returns Sunday; adding 7 returns Saturday.
In WPS Spreadsheet, select cell A1 and enter the date for the week you want to calculate.
Select the result cell for Sunday and enter =A1-WEEKDAY(A1,1)+1, then press Enter.
Select the result cell for Saturday and enter =A1-WEEKDAY(A1,1)+7, then press Enter.
Select both result cells, open the Home tab, choose the Number Format menu, and apply a Date format if the results display as serial numbers.
Calculate Weekly Sunday and Saturday Dates in WPS Spreadsheet
WPS Spreadsheet lets you enter the source date, apply WEEKDAY formulas, and format the calculated week boundaries as readable dates in just a few steps.
- 1. Open WPS Spreadsheet: Launch WPS Office and open a blank spreadsheet or the workbook containing your date.
- 2. Enter the source date: Type a valid date in cell A1.
- 3. Add the Sunday formula: In a nearby cell, enter =A1-WEEKDAY(A1,1)+1 and press Enter.
- 4. Add the Saturday formula: In another nearby cell, enter =A1-WEEKDAY(A1,1)+7 and press Enter.
- 5. Apply date formatting: Select the formula results, open Home > Number Format, and choose a date style.
Frequently Asked Questions
What formula returns the Sunday of the week for a date in A1?
Enter =A1-WEEKDAY(A1,1)+1 in a result cell. With the return type set to 1, Sunday is treated as the first day of the week.
How can I calculate the Saturday date from the same date?
Enter =A1-WEEKDAY(A1,1)+7. This returns the Saturday belonging to the same Sunday-through-Saturday week as the date in A1.
What happens when the date in A1 is already Sunday?
The Sunday formula returns the same date because WEEKDAY(A1,1) equals 1. The Saturday formula returns the following Saturday.
Why does WPS Spreadsheet show a number instead of a date?
The formula may be returning a date serial number. Select the result cell, go to Home > Number Format, and choose a Date format.
