'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Automatically Fill Consecutive Sunday Dates Across Excel Columns
Question details
The user needs to automatically generate a sequence of consecutive Sunday dates across multiple columns in Excel.
- Product
- Excel
- Device & OS
- Windows 11
- Scenario
- Creating a weekly schedule, calendar, or tracker that stretches across multiple horizontal columns.
- Observed behavior
- Requires a dynamic formula or feature to populate dates incrementing exactly by 7 days (Sundays) across columns without manual entry.
Ensure you have enough empty columns to the right of your starting cell so the formula can spill the results without encountering a #SPILL! error.
Use the SEQUENCE Function to Fill Weekly Dates
The SEQUENCE function is the most efficient and dynamic way to generate an array of weekly dates across columns.
By utilizing Excel's SEQUENCE function combined with the DATE function, you can output a defined number of weeks spaced exactly 7 days apart. This method requires Excel 365, Excel 2021, or newer.
Click on cell B1 (or your preferred starting cell) where you want the first Sunday date to appear.
Type the formula =SEQUENCE(,52,DATE(2025,1,5),7) and press Enter. Adjust the '52' to the number of columns you want to fill, and change 'DATE(2025,1,5)' to your specific starting Sunday.
Highlight the generated range (e.g., B1:BA1). Right-click, select 'Format Cells', navigate to the 'Number' tab, choose 'Date', and pick your desired date format.
Use a Basic Addition Formula for Older Excel Versions
If you are using an older version of Excel that does not support dynamic array functions like SEQUENCE, you can use a simple addition formula.
Effortlessly Manage Dates and Schedules in WPS Spreadsheet
WPS Spreadsheet provides robust dynamic array functions like SEQUENCE, allowing you to quickly generate weekly dates and build comprehensive schedules without hassle.
- 1. Open a new spreadsheet: Launch WPS Office and create a new blank spreadsheet.
- 2. Input the sequence formula: Click your target cell and type =SEQUENCE(, 52, DATE(2025,1,5), 7).
- 3. Apply date formatting: Highlight the generated cells, press Ctrl + 1 to open the Format Cells dialog, and select your preferred Date display format.
Frequently Asked Questions
Why am I getting a #SPILL! error when using the SEQUENCE function?
This error occurs when the adjacent cells where the formula needs to place the dates are not completely empty. Clear any data or spaces in the columns to the right to make room for the array.
How can I change the formula to generate dates down a column instead of across rows?
To spill dates vertically down a single column instead of horizontally across columns, move the number of weeks to the rows argument. Use =SEQUENCE(52,,DATE(2025,1,5),7).
Does the SEQUENCE formula work in older versions of Excel?
No, the SEQUENCE function is a dynamic array function available only in Microsoft 365, Excel 2021, and newer versions. For older versions, use the AutoFill drag handle or a basic formula like =B1+7.
