Function Problems

How to Create Sequential Dates Across Multiple Excel Worksheets

Maira Mehtab Sep 27, 2026 875 views

Question details

The user wants to generate sequential dates across multiple consecutive worksheets so that Sheet 1 displays the starting date (e.g., October 1), Sheet 2 displays the next day (October 2), and so on.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Creating daily logs, monthly timesheets, or schedule trackers where each worksheet represents a consecutive day in a specific timeframe.
Observed behavior: Dates are currently being typed manually on each individual worksheet, which is time-consuming and does not update dynamically if the starting date changes.

Before you start

Ensure that all the worksheets you need are already created in your workbook and that you have identified a consistent cell location on each sheet to place the date and sequence number.

Solution 1Recommended

Use Sheet Grouping and a Date Addition Formula

By assigning a sequence number to each sheet and utilizing Excel's sheet grouping feature, you can apply a single formula that dynamically calculates the date for every worksheet based on the starting date.

This method relies on creating a logical link between a master starting date on your first sheet and a sequential reference number placed on all subsequent sheets.

Assign a sequence number to each worksheet

Navigate through your worksheets and enter a sequence number in a specific cell (e.g., cell B1). Type '1' in B1 on Sheet 1, '2' in B1 on Sheet 2, '3' on Sheet 3, and so forth until the last sheet.

Enter the starting date

Go to your first worksheet (Sheet 1). In your designated date cell (e.g., cell C1), type your starting date, such as '10/1/2023'.

Group the remaining worksheets

Click on the tab for Sheet 2. Then, hold down the 'Shift' key on your keyboard and click on the tab of your final worksheet. This will group all sheets from Sheet 2 to the end, allowing you to edit them simultaneously.

Enter the sequence formula

While the sheets are grouped, click on the target date cell (e.g., C1) and type the formula: =Sheet1!C1+B1-1. Press 'Enter' to apply it.

Ungroup and verify

Right-click any of the grouped sheet tabs at the bottom and select 'Ungroup Sheets'. Check your worksheets to verify that each sheet now displays the correct sequential date. If you change the date on Sheet 1, all other sheets will automatically update.

Format Cells as Date: If the formula result displays as a 5-digit number (like 45200) instead of a date, select the cell, go to the Home tab, and change the Number Format dropdown from 'General' to 'Short Date'.

Efficient Spreadsheet Management

Easily Manage Multi-Sheet Formulas with WPS Spreadsheet

WPS Spreadsheet provides a seamless environment for handling multi-sheet calculations, batch editing, and advanced date functions, making it incredibly easy to manage dynamic daily schedules and logs.

1. Open your workbook: Launch WPS Spreadsheet and open the workbook containing your daily worksheets.
2. Set sequence numbers and start date: Input your sequence numbers in cell B1 across your sheets, and your master start date in cell C1 of the first sheet.
3. Group and apply formula: Hold 'Shift' to select all target sheet tabs, click cell C1, type =Sheet1!C1+B1-1, and press Enter to instantly generate all sequential dates.

Fully compatible with Microsoft Excel (.xlsx) formats and formulas, ensuring your date functions work flawlessly.Intuitive sheet grouping features for rapid batch data entry across dozens of worksheets.Lightweight, fast, and completely free to use for everyday office tasks.Familiar user interface with built-in date formatting and calendar tools.

microsoft office alternative - wps office

Frequently Asked Questions

Can I generate sequential dates without manually typing sequence numbers on every sheet?

Yes. If your sheets are named systematically (e.g., Day1, Day2, Day3), you can use a combination of the MID, CELL, and FIND functions to extract the number directly from the sheet tab name, eliminating the need for a helper sequence cell.

What happens if I insert a new sheet in the middle of my workbook?

A newly inserted worksheet will not automatically inherit the formula or the sequence number. You will need to manually add the correct sequence number in the designated cell and copy the date formula from an adjacent sheet.

Why is my date changing to a random five-digit number?

Spreadsheet programs store dates as serial numbers to perform calculations. If you see a number like 44104, the cell is formatted as 'General' instead of 'Date'. Simply select the cell, right-click, choose 'Format Cells', and select the Date format to fix it.

Excel & Data Problems