How to Enter Sequential Dates Across Multiple Sheets in Excel

Get FREE Advanced Excel Exercises with Solutions!

In this Excel tutorial, you will learn how to enter sequential dates across multiple sheets in Excel. For example, you have 10 worksheets in an Excel workbook, and you want to enter the sequential dates 1-10-2023 in sheet 1, 2-10-2023 in sheet 2, 3-10-20203 in sheet 3, and so on. In the following image, you see, that we have entered sequential dates in each worksheet by using Excel formula.

excel sequential dates across Sheets

Using Excel formula to enter sequential dates across sheets

Entering sequential dates across sheets can be done in two ways:

– Using Excel formula by combining DATE, CELL, REPLACE, and SEARCH functions

– Applying VBA code. We can use the VBA code to enter the sequential dates in a single cell or in a range of cells.

People need to enter sequential dates in Excel sheets for various purposes like record keeping, preparing sales data, creating attendance sheets, calendar planning, etc.


What are Sequential Dates and How to Create them in Excel?

Sequential dates are a series of dates that maintain a specific interval. For example, if you have a series of dates like January 1, 2023, January 2, 2023, January 3, 2023, and so on, these dates are sequential because they follow a continuous and consecutive pattern.

In Excel, you can easily create sequential dates by using the AutoFill drag-down. This is a sample dataset where we need the sequential dates in the Date column.

Follow the steps to create sequential dates:

Step 1: Enter the first date in the first cell of the column where you need to fill in the sequential dates. We entered 01-10-2020 in cell B6.

Using  fill series to enter sequential dates

Step 2: Take the mouse over the bottom-right corner of the cell B6.

=> The cursor will be changed from the White Plus sign to Green Plus. This Green Plus is our Fill Handle feature.

=> Drag the Fill Handle and AutoFill the formulas up to the last row of data. You will get the sequential dates. In the Auto Fill options, you can see the Fill Series option is used by Excel automatically.

Read More: How to Repeat Formula Pattern in Excel


1. Using Excel Formula to Enter Sequential Dates Across Sheets in Excel

In this method, we shall use Excel Formula to enter sequential dates across sheets. We shall use a formula with the combination of DATE, REPLACE, CELL, and SEARCH functions. These functions will work together to provide the sequential dates across the sheets.

We shall enter the formula in a cell of the first sheet and then copy the formula to the rest of the worksheets. This formula will populate the sequential dates in all the worksheets.

Following is the dataset we will use.

You can see, we have 10 worksheets in an Excel workbook. The names of the worksheets are 1,2,3,4,…..10. Each sheet contains the order details of a specific date. We want to enter the sequential dates from 1-10-2023 to 10-10-2023 in cell C4 of the worksheets.

Using Excel formula to enter sequential dates across sheets

Follow the steps:

Step 1: Write this formula in cell C4 and press Enter.

=DATE(2023,10,REPLACE(CELL("filename",A1),1,SEARCH("]",CELL("filename",A1),1),""))

You will get the date 01-10-2023 in cell C4 of the worksheet named 1.

Applying excel formula to enter series of dates

Formula Breakdown

=DATE(2023,10,REPLACE(CELL(“filename”,A1),1,SEARCH(“]”,CELL(“filename”,A1),1),””))

=DATE(2023,10,REPLACE(CELL(“filename”,A1),1,SEARCH(“]”,”C:\Users\Admin\Downloads\[Excel_Formula_to_Enter_Sequential_Dates_Across_Sheets.xlsx]1“,1),””)) // CELL(“filename”,A1),1) returns C:\Users\Admin\Downloads\[Excel_Formula_to_Enter_Sequential_Dates_Across_Sheets.xlsx because it is the filename of the Excel workbook.

=DATE(2023,10,REPLACE(CELL(“filename”,A1),1,85,””))// SEARCH(“]”,”C:\Users\Admin\Downloads\[Excel_Formula_to_Enter_Sequential_Dates_Across_Sheets.xlsx]1″,1) returns 85 because it searched the position of “]” in the file name and it is at the 85th position.

=DATE(2023,10,REPLACE(C:\Users\Admin\Downloads\[Excel_Formula_to_Enter_Sequential_Dates_Across_Sheets.xlsx]1“,1,85,””))// CELL(“filename”,A1) returns the filename where the cell A1 locates.

=DATE(2023,10,”1“)// REPLACE(“C:\Users\Admin\Downloads\[Excel_Formula_to_Enter_Sequential_Dates_Across_Sheets.xlsx]1″,1,85,””) returns 1 because it replaces the string of 85th position with 1. This part will sequentially replace the value 1 with 2,3,4,5…..10 and the sheet name will change.

=10/1/2023// DATE(2023,10,”1″) returns 10/1/2023 because this is the date we entered by giving the year, month, and date.

Step 2: Now, we have to copy this formula in cell C4 of the rest of the worksheets. For this, we shall select all the sheets and copy this formula into all the sheets at once. To do this,

=>Click on the second worksheet named 2.

=>Pressing the Shift key, select the last worksheet named 10.

Multi selecting worksheets in excel

Therefore, you have multi-selected the worksheets from 2 to 10 at once.

Selecting multiple sheets in Excel

Step 3: Keeping all the worksheets selected, write this formula in cell C4 of worksheet 2 and press the Enter button.

=DATE(2023,10,REPLACE(CELL("filename",A1),1,SEARCH("]",CELL("filename",A1),1),""))

You will get the next date 02-10-2023 in cell C4 of worksheet 2.

Copying formula to the selected Excel sheets

Step 4: Now, check all the sheets one by one and you will get the sequential dates from 01-10-2023 to 10-10-2023.

Using Excel formula to enter sequential dates across sheets

If you have a very large number of sheets, copying this formula in sheets may seem difficult. Another effective way is to use a VBA code to autofill data. Let’s move to another method which is applying VBA code to enter sequential dates.


2. Using VBA Macro to Get Sequential Dates Across Sheets in Excel

In this section, we shall use a VBA code to get sequential dates across sheets in Excel. We will explain two examples here. The first one is entering sequential dates in a single cell across sheets. Another one is to enter sequential dates in a range of cells across sheets.

We shall use the same VBA code for both of the cases. Let’s start with the first example.


2.1 Entering Sequential Dates in a Single Cell Across Sheets

In this example, you will learn to use VBA code to auto populate sequential dates in single cells across sheets.

Following is the workbook we are going to use.

You can see, we have 10 worksheets. Each sheet is created for the order details of one day. The cell C4 is kept for entering the date. We have to enter sequential dates in cell C4 of each sheet.

Dataset for using VBA code to enter dates

Follow the steps:

Step 1: To write any VBA code, you must launch the VBA editor first. To do that,

=>Click on the Developer tab from Excel Ribbon. If you don’t have the Developer tab in Excel Ribbon, you have to enable it from Excel Options.

=>Afterward, select the Visual Basic option.

Note: Alternatively, you can press Alt+F11 to open the VBA editor.

Developer tab in Excel

Step 2: After clicking on Visual Basic, Excel will lead you to the VBA Editor Window. In this window, you can write your VBA code.

VBA code editor

Step 3: Now, select Insert => Click on Module.

Inserting Module in VBA code editor

Step 4: Copy this code in Module1 =>Save this code by pressing Ctrl+S.

Sub SEQUENTIAL_DATES()
    Dim active_workbook As Workbook
    Set active_workbook = ActiveWorkbook
    
    Dim sheet_arr() As Variant
    Dim total_sheets As Variant
    
    total_sheets = active_workbook.Sheets.Count - 1
    
    ReDim sheet_arr(total_sheets)
    
    Dim i As Integer
    For i = 0 To total_sheets
        sheet_arr(i) = active_workbook.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(sheet_arr(0)).Range(Selection(1).Address).Formula = First_Date
    
    Dim j As Integer
    Dim k As Variant
    
    For j = 0 To UBound(sheet_arr)
        For Each k In Selection
            Sheets(sheet_arr(j)).Range(k.Address) = Sheets(sheet_arr(0)).Range(Selection(1).Address).Value + Increment * Count
            Count = Count + 1
        Next k
    Next j
End Sub 
			

 

VBA Code to Enter Sequential Dates in a Single Cell Across Sheets in Excel

Click the image for detailed view

Step 5: Now, select the cell C4 of the first sheet, “Sheet1” =>Click on the Developer tab =>Code group of commands =>Select Visual Basic.

VBA code editor will appear.

Visual Basic option of Developer tab

Step 6:

=>Click on Run in VBA code Editor =>Click on Run Sub/Macro.

Running the VBA Macro

Alternatively, you can press the F5 key to Run the VBA code.

=>Microsoft Excel dialog box will appear. Enter the first date you want to show here and press OK. I have entered 1/1/2023 here.

Entering the first date

Step 7: Then Enter the Increment value and press OK. I have entered the Increment value as 1.

Note: If you want the same date in each worksheet, the increment will be 0. If you want to enter 2-01-2020 in the second worksheet, 3-01-2023 in the third worksheet, and so on, the increment will be 1. Again, if you want to enter 3-01-2023 in the second worksheet, 5-01-2023 in the third worksheet, and so on, the increment will be 2.

Entering the Increment

As a result, you will get the sequential dates in all the worksheets till the last rows using VBA code.

Using VBA to enter sequential dates across sheets in Excel


2.2 Entering Sequential Dates in a Range of Cells Across Sheets

In this example, we shall show you how to enter sequential dates in a range of cells across sheets. We are going to use the same VBA code here. The difference is, that we shall enter the sequential dates in a range of cells here.

This is the sample dataset we shall use. We have 10 worksheets. We want to enter sequential dates in the Ship Date column of each worksheet. We shall do this using the previous VBA code.

Dataset for entering sequential dates in a range of cells

Follow the steps:

Step 1: Copy this code again in this new Workbook.

Sub SEQUENTIAL_DATES()
    Dim active_workbook As Workbook
    Set active_workbook = ActiveWorkbook
    
    Dim sheet_arr() As Variant
    Dim total_sheets As Variant
    
    total_sheets = active_workbook.Sheets.Count - 1
    
    ReDim sheet_arr(total_sheets)
    
    Dim i As Integer
    For i = 0 To total_sheets
        sheet_arr(i) = active_workbook.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(sheet_arr(0)).Range(Selection(1).Address).Formula = First_Date
    
    Dim j As Integer
    Dim k As Variant
    
    For j = 0 To UBound(sheet_arr)
        For Each k In Selection
            Sheets(sheet_arr(j)).Range(k.Address) = Sheets(sheet_arr(0)).Range(Selection(1).Address).Value + Increment * Count
            Count = Count + 1
        Next k
    Next j
End Sub 
			

 

VBA Code to Enter Sequential Dates in a Range of Cells Across Sheets in Excel

Click the image for a detailed view

Step 2: Select the range of cells where you want to get the sequential dates =>Click on the Developer tab =>Code group of commands =>Select the Visual Basic option.

Visual Basic option of Developer tab

Step 3: Now, Run the code from the VBA code editor.

=> Enter the first date you want and press OK.

=>Enter the increment value and press OK. I have put this value as 3 because I want to get the dates 3 days after the first date.

Therefore, you will get the sequential dates in all the worksheets.

Using VBA Macro to Enter Sequential Dates in a Range of Cells Across Sheets in Excel

Read More: How to Autofill Dates in Excel Without Dragging


Download Practice Workbook


In this Excel tutorial, you have learned how to enter the sequential dates across sheets. We explained 2 effective ways to enter sequential dates. These are: using Excel formula by combining CELL, REPLACE, SEARCH, and DATE functions and using VBA code. By applying the VBA code, you can not only enter sequential dates in a single cell of multiple worksheets but also in a range of cells. We hope, from this tutorial, you have learned to enter sequential dates across sheets.


Further Readings


<< Go Back to Autofill Dates | Excel Autofill | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rifat Hassan
Rifat Hassan

Rifat Hassan, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, has worked with the ExcelDemy project for almost 2 years. Within these 2 years, he has written over 250 articles. He has also conducted a few Boot Camp sessions on effective coding, especially Visual Basic for Applications (VBA). Currently, he is working as a Software Developer to develop and deploy additional add-ins to enhance the customers with a more sophisticated experience with Microsoft Office Suits,... Read Full Bio

6 Comments
  1. Hi do you know why when I enter in an increment of 1 it displays every 2 days? If I enter 0.5 it makes it the same date. Not sure what I am doing wrong

    • Thank you Chelsea for your comment. We have checked the code and it’s working perfectly on our end. Here, we have set the date as

      =DATE(2022,8,21)

      and 1 as the increment.

      VBA Code Working OK

      Did you follow the steps correctly? If yes, then you can send your Excel file and we can take a look into that.

  2. When I try to run the macro I receive an error and break at line, “Sheets(out(j)).Range(k.Address) = Sheets(out(0)).Range(Selection(1).Address).Value + Increment * Count”

    I have an individual sheet for Monday, Tuesday, Wednesday, Thursday, and Friday.
    I’m entering a date 12/28/22, and would like Tuesday to add “1” day to populate the next date aka 12/29/22, Wednesday to be +1 being 12/30/22 and so forth.

    So, the first time the macro ran it asked First date I put “=Monday!E1” then Increment “1”

    Now I don’t know how to fix the error and get it to run again. Any needs or questions please reach out.

    • Greetings. You must carry out the subsequent steps if you want to resolve the issue.
      When you run the VBA code, 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 in Excel.
      To fix your problem, you have to enter the formula:
      =DATE(2022,12,28)
      Year, month, and day are shown, respectively, as (2022,12,28).
      Next, click on OK.
      Now, another Microsoft Excel prompt box will appear that will ask you to enter the increment.
      Enter the increment, and then click on the OK button.

  3. I have copy and pasted the code into VBA but when I run the macro the increment increases by 2 weeks. I use =date() and put in an increment of 1.

    • Reply Avatar photo
      Md. Abdur Rahim Rasel Jun 21, 2023 at 12:36 PM

      Hello, Robert Scott!
      Thanks for sharing your problem with us!
      We have checked the code and it’s working perfectly on our end. Here, we have set the date as

      =DATE(2022,8,5)

      and 1 as the increment.
      Interview Schedule of XYZ Group
      If you change the code as shown in the screenshot, the macro will increase the increment by 2 weeks. Otherwise, the macro will return the increment by 1 week.
      Using VBA code to enter Sequential Dates
      If you cannot solve your problem, please mail us at the address below.
      [email protected]
      Regards
      Md. Abdur Rahim Rasel (Exceldemy Team)

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo