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.
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.
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.
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.
=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.
Therefore, you have multi-selected the worksheets from 2 to 10 at once.
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.
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.
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.
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.
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.
Step 3: Now, select Insert => Click on Module.
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
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.
Step 6:
=>Click on Run in VBA code Editor =>Click on Run Sub/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.
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.
As a result, you will get the sequential dates in all the worksheets till the last rows using VBA code.
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.
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
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.
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.
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
- How to AutoFill Months in Excel
- How to Create Automatic Rolling Months in Excel
- How to Increment Month by 1 in Excel
- How to Autofill Days of Week Based on Date in Excel
- How to Fill Down Blanks in Excel
- How to Add Sequence Number by Group in Excel
- How to Repeat Number Pattern in Excel
- How to Perform Predictive AutoFill in Excel
<< Go Back to Autofill Dates | Excel Autofill | Learn Excel
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
and 1 as the increment.
Did you follow the steps correctly? If yes, then you can send your Excel file and we can take a look into that.
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.
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.
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
and 1 as the increment.
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.
If you cannot solve your problem, please mail us at the address below.
[email protected]
Regards
Md. Abdur Rahim Rasel (Exceldemy Team)