How to Make Excel WEEKDAY Return the Current Sunday as the Week Start
Question details
The user needs a formula using the WEEKDAY function to calculate and return the Sunday of the current week, assuming Sunday is the first day.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Calculating weekly start and end dates where Sunday is the official start of the week.
- Observed behavior
- The formula needs to accurately return the current week's Sunday instead of the previous Sunday, even if the given date is already a Sunday.
Ensure you have a cell containing a valid date value formatted as a date, not as plain text, before applying the WEEKDAY formula.
Calculate the Current Sunday Using the WEEKDAY Function
Use this standard formula offset to dynamically find the Sunday that starts the current week.
By utilizing the WEEKDAY function with a return type of 1, you can accurately determine the numerical day of the week for any given date. Subtracting this value from the original date and adding an offset of 1 ensures the formula always lands exactly on the current Sunday.
Click on an empty cell where you want the week's start date (Sunday) to appear.
Type the formula =A1-WEEKDAY(A1,1)+1 into the formula bar, replacing A1 with the cell reference that contains your original date.
Press Enter to apply the formula. If the result displays as a random 5-digit number, right-click the cell, choose 'Format Cells', and select 'Date' from the Number tab to display it correctly.
To calculate the week-ending date (Saturday), select another empty cell and use the formula =A1-WEEKDAY(A1,1)+7.
Easily Manage Date Formulas in WPS Spreadsheet
WPS Spreadsheet fully supports Excel's WEEKDAY function and offers a highly compatible, user-friendly interface for all your data calculation and formatting needs.
- 1. Open WPS Spreadsheet: Launch WPS Office and open your spreadsheet file containing the dates.
- 2. Access the Insert Function tool: Click the 'Formulas' tab on the top ribbon and select 'Insert Function'.
- 3. Search for WEEKDAY: Search for 'WEEKDAY' in the dialog box and follow the intuitive prompts to set your date reference and return type.
- 4. Apply the Sunday offset: Apply the formula =A1-WEEKDAY(A1,1)+1 to instantly get the Sunday week start.
Frequently Asked Questions
What if my week starts on Monday instead of Sunday?
To start your week on Monday, change the return type in the WEEKDAY function to 2. The formula to find the current Monday would be =A1-WEEKDAY(A1,2)+1.
Why does my WEEKDAY formula return a random 5-digit number?
Spreadsheet programs store dates as serial numbers for calculation purposes. If your formula returns a 5-digit number like 44500, simply right-click the cell and change the formatting to a Short Date or Long Date format.
Does the WEEKDAY function work the same in WPS Office as it does in Excel?
Yes, the WEEKDAY function in WPS Spreadsheet is fully compatible with Microsoft Excel, using the exact same syntax, return types, and date serial numbers.
Can I find the last day of the month using a similar formula?
To find the last day of a month, it is much easier and more accurate to use the EOMONTH function, such as =EOMONTH(A1, 0), rather than trying to calculate it using the WEEKDAY function.
