Formula Errors

How to Create Excel Formulas for Dates in Nonadjacent Columns

Huma Ashraf Ch Sep 29, 2026 869 views

Question details

The user needs to build a repeating horizontal Excel layout where each day spans five columns, requiring formulas to automatically increment dates, weekdays, and day numbers in nonadjacent cells.

How to Set Excel Formulas for Dates and Days in Nonadjacent Columns

Product: Excel
Device & OS: not provided
Scenario: Creating a horizontal daily schedule, tracker, or calendar template with multiple columns dedicated to each day.
Observed behavior: Looking for the correct formula structure to calculate and increment dates correctly when pasted into every fifth column, rather than sequentially.

Before you start

Ensure your worksheet has enough space, as a full 365-day horizontal layout using five columns per day requires 1,825 columns, which is well within Excel's limit but will require extensive scrolling.

Solution 1Recommended

Use Relative Cell Referencing for Nonadjacent Cells

The most straightforward method to increment dates across groups of columns is to reference the previous group's date cell and simply add 1.

While complex dynamic arrays can automate calendar generation, a traditional manual setup provides more flexibility if you need to input manual data within the empty cells of your 5-column blocks.

Enter the starting date

Click on the cell where you want your first date to appear (for example, cell D1) and type your initial date.

Set up the next nonadjacent block

Count five columns over to your next date cell (for example, cell I1). Enter the formula =D1+1 to calculate the next consecutive date.

Format day numbers and weekdays

If you need a specific day label, type ="Day "&1 in your first block. In the next block, use ="Day "&2 (or reference the previous cell number and add 1). To show the weekday, you can format the date cell to Custom and type "dddd".

Copy the repeating layout

Select the entire second 5-column block (from I1 to M1, assuming your blocks are 5 columns wide). Click and hold the fill handle at the bottom right of the selection, and drag it to the right across your worksheet to auto-fill the subsequent blocks.

Use Relative Cell Referencing for Nonadjacent Cells

Layout Considerations: A 365-day horizontal layout can become difficult to navigate. Consider restructuring your data into a vertical table with one row per date, or break it into separate 7-day worksheet sections for easier management.

Solution 2

Generate a Dynamic Array Template

Use a dynamic array formula to generate a calendar template if manual data entry inside the generated formula cells is not required.

Build Advanced Multi-Column Layouts Easily with WPS Office

WPS Spreadsheet provides powerful formula support, massive grid capabilities, and an intuitive interface to help you build complex daily trackers and repeating multi-column layouts effortlessly.

1. Create a new spreadsheet: Open WPS Office, select Spreadsheet, and click Blank to create your new tracker.
2. Enter your base formulas: Type your initial date and use the +1 formula method in the adjacent 5-column block.
3. Drag to copy across the year: Highlight your formulated block and use the smart fill handle to drag horizontally across the sheet to generate your complete timeline.

100% compatible with Microsoft Excel formulas, formatting, and file types.Lightweight architecture handles massive sheets with thousands of columns smoothly.Seamless support for relative referencing and dynamic drag-and-drop fills.Completely free to download and use with a familiar ribbon interface.

microsoft office alternative - wps office

Frequently Asked Questions

How many columns can an Excel worksheet hold for horizontal templates?

Modern spreadsheet applications, including Excel and WPS Spreadsheet, support up to 16,384 columns (from column A to XFD). This easily accommodates a 365-day layout using five columns per day (which requires 1,825 columns).

Why do I get a #SPILL! error when using array formulas for calendars?

A #SPILL! error occurs when the formula's calculated output range is blocked by data already existing in those cells. You must clear the adjacent cells in the spill path to allow the dynamic formula to display its full results.

How do I display only the weekday name from a date cell?

You can display just the weekday without changing the underlying date value by selecting the cell, opening the Format Cells dialog (Ctrl+1), choosing Custom format, and entering "dddd" in the Type field. Alternatively, you can use the formula =TEXT(D1,"dddd").

Excel & Data Problems