How to Enter Sequential Dates Across Multiple Sheets in Excel

In this regard, When working with dates in Excel, we have learned to enter dates in our various desired orders. Besides, we have learned to enter a range of sequential dates. But, sometimes, it might be needed to enter sequential dates in multiple sheets according to their purposes. In this article, I will show you 2 suitable ways to enter sequential dates across multiple sheets in Excel.

Download Practice Workbook

You can download our practice workbook from here for free!

2 Suitable Ways to Enter Sequential Dates Across Multiple Sheets in Excel

1. Using VBA Macro to Enter Sequential Dates Across Multiple Sheets in Excel

We can enter sequential dates across multiple sheets by using a VBA macro in Excel. In this regard, we can accomplish this task for both single cell or a range of cells according to our requirements. We will show you both examples here to make your understanding clear.

In doing this, we will accomplish this in two steps.

Step 1: We will generate a Macro using VBA Code.

Step 2: We will utilize the generated Macro to Enter sequential dates across all worksheets.

Example 1: Entering Sequential Dates in a Single Cell Across Sheets

Here, we’ve got a workbook with five worksheets. Each worksheet contains the total sales of one day of a company named Marco group.

Our objective today is to enter sequential dates across the worksheets. For example, if the Date in Worksheet “Day 1” is May 02, 2021. The Date in Worksheet “Day 2” will be May 02, 2021. That in Worksheet “Day 3” will be May 03, 2021. And so on.

📌 Step 1: Generating a Macro to Enter Sequential Dates

• First and foremost, go to the Developer tab >> Visual Basic tool. Thus, you will go to the VB Editor. You can also use the Alt + F11 key on your keyboard to do this.

• Afterward, go to the Insert tool in the upper left corner.
• Following, select Insert >> Module. A new module will be opened for writing VBA code.

• At this time, insert the following VBA code and paste it in the created module.

Sub SEQUENTIAL_DATES()
Dim mainworkBook As Workbook
Set mainworkBook = ActiveWorkbook
Dim out As Variant
Dim x As Variant
x = mainworkBook.Sheets.Count - 1
ReDim out(x)
Dim i As Variant
For i = 0 To x
    out(i) = mainworkBook.Sheets(i + 1).Name
Next i
First_Date = InputBox("Enter the First Date: ")
Increment = CInt(InputBox("Enter the Increment: "))
Dim Count As Integer
Count = 0
Sheets(out(0)).Range(Selection(1).Address).Formula = First_Date
Dim j As Integer
Dim k As Variant
For j = 0 To UBound(out)
    For Each k In Selection
        Sheets(out(j)).Range(k.Address) = Sheets(out(0)).Range(Selection(1).Address).Value + Increment * Count
        Count = Count + 1
    Next k
Next j
End Sub

• Afterward, press Ctrl + S to save the Excel file with the VBA code.

• In this regard, you must save the file as .xlsm (Excel Macro-Enabled Workbook) file. Otherwise, the macro won’t work.

📌 Step 2: Entering Sequential Dates in Multiple Sheets Using That Macro

After generating the Macro, now, you will need to run that macro properly.

• To do this, come back to any of your Excel Worksheets and select the cell where you want to enter the sequential dates. In this example, I am selecting cell C4.
• Afterward, go to the Developer tab >> Macros tool. You can also press Alt + F8 on your keyboard to open the Macro window.

• As a result,  the Macro dialogue box containing all the Macros will be opened.
• Following, select SEQUENTIAL_DATES macro and click on the Run button.

• At this time, you will find a Microsoft Excel prompt box asking you to enter the first date.
• Subsequently, enter the date that you want to insert in the first worksheet. Enter it using the DATE function of Excel.
• For the sake of this example, I am entering January 01, 2022, through the following formula and clicking on the OK button.

• Now, another Microsoft Excel prompt box will appear that will ask you to enter the increment.
• Enter the increment. If you want the same date in each worksheet, the increment is 0. If you want to enter January 2 in the second worksheet, January 3 in the third worksheet, and so on, the increment is 1. Again if you want to enter January 3 in the second worksheet, January 5 in the third worksheet, and so on, the increment is 2.
• For the sake of this example, I am entering increment 2 and then clicking on the OK button.

Thus, You will find a sequential date generated in the same cell of each worksheet, with a step increment of 2 days. And, the final outcome of the sheets would look like this.

Example 2: Entering Sequential Dates in a Range of Cells Across Sheets

This code will be also useful if you want to enter sequential dates in a range of cells across worksheets. Look at this data set.

We have a workbook consisting of three worksheets. Each worksheet contains the names of some candidates for an upcoming interview in the Marco Group.

Now, we want to enter sequential dates for all the interview dates of all the worksheets. To achieve this, follow the steps below.

📌 Steps:

• First, repeat the step 1 procedures from the previous example to insert the VBA macro in the Excel file.
• Next, select all the cells (C5:C8 here) in any worksheet >> go to the Developer tab >> Macros tool.

• As a result, the Macro window will appear.
• Following, choose the SEQUENTIAL_DATES macro and click on the Run button.

• As a result, a Microsoft Excel dialogue box will appear asking for the first date.
• Following, insert the following formula in the input text box and click on the OK button to start the dates for January 1, 2022.

• At this time, another Microsoft Excel dialogue box will appear asking for the increment.
• Subsequently, put 2 inside the text box and click on the OK button to get dates at the one-day interval.

Thus, you will find all the selected cells from all the worksheets that have been filled with sequential dates, in your given increment. And, the final outcome would look like this.

Read more: How to Autofill Dates in Excel

2. Enter Sequential Dates According to Sheet Names in Excel Using Formula

Now I will be showing how you can extract the sheet name in any cell in the worksheet, and enter the dates according to the name.

Here we’ve got a workbook with three workbooks, with the names “1”, “2”, and “3” respectively. Our objective is to enter the sheet name as the day of the date, keeping the month and the year fixed.

For example, the sheet with the name “1” will have January 01, 2022. The sheet with the name “2” will have January 02, 2022. And so on.

To achieve this, we shall use a combination of the DATE, REPLACE, CELL, and SEARCH functions of Excel. Follow the steps below to do this.

📌 Steps:

• First, go to each worksheet and enter this formula in place (cell C4 here) of the date.

• Here, within the DATE function, 2022 and 1 are the years and months respectively (January 2022). You can use it according to your needs. The REPLACE function returns the sheet name and that is used as the day of the date.
• Now, keep the rest of the formula unchanged, and insert it in all the worksheets.

Thus, you will be able to enter the sequential dates across sheets in Excel according to the sheets’ names. And, the final output should look like this.

How to Autofill Dates in Excel in the Same Sheet Without Dragging

Now, sometimes, it might appear that you need to autofill dates in a single worksheet but without dragging the fill handle.

Say, you have a dataset with Student ID and their date of admission. Every student is admitted every day beginning from 1-Jan-2022. Now, you want to input only the beginning dates. But, you want to get all the other dates sequentially without dragging the fill handle.

Follow the steps below to accomplish this.

📌 Steps:

• First, click on the first input cell (Cell C5 here).
• Afterward, go to the Data tab >> Data Tools group >> Flash Fill option.

Thus, you will see that the dates will be automatically filled to the cells C6:C10 and the result should look like this.

Conclusion

So, in this article, I have shown you two suitable ways to enter sequential dates across sheets in Excel. You can also download our free workbook to practise. I hope you find this article helpful and informative. If you have any further queries or recommendations, please feel free to comment here.

And, visit ExcelDemy to learn more things about Excel! Have a nice day!

Further Readings

• How to AutoFill Cell Based on Another Cell in Excel (5 Methods)
• Automatic Numbering in Excel (9 Approaches)
• How to Auto Populate Cells in Excel Based on Another Cell
• Autofill Numbers in Excel (12 Ways)
• How to Use Autofill Formula in Excel (6 Ways)