4 Suitable Ways to Add Sequential Dates Across Sheets in Excel

Generating data using various formulas is quite common in Microsoft Excel. In the case of creating data separately for sequential dates, it is difficult to manually date calculate or mention. Now there are 4 ways to add sequential dates across Excel sheets automatically or using a few shortcuts. You don’t have to look out for the previous sheet date and continue the methods throughout the sheets. Whether it is automatic or formula based, you will get to know easy tricks with the help of a few clicks. All the methods have step-by-step instructions with a figurative description.

📁 Download Excel Files

Download the Excel files below.

Learn to Add Sequential Dates Across Excel Sheets with These 4 Approaches

The article is going to introduce four methods to create sequential dates across Excel sheets. The methods will provide simple to complex solutions gradually. The simple solution involves sheet increments, a combination of DATE, REPLACE, CELL, and SEARCH functions. On the other hand, complex ones will be covering UDF PrevSheet and Macro-Enabled Workbook. Users will get this versatile walkthrough of these solutions to one of the fundamental problems.

Here, you will see examples on worksheets named Day 1, Day 2, and Day 3.

⬇️⬇️ STEPS ⬇️⬇️

• First, enter the starting date on Day 1.
• Second, click on B6 in sheet Day 2 and type “=” in it.

• Third, click on the previous date sheet Day 1.
• Now, click on B6.
• Type “+1” with it.
• Here, the formula should be visible as follows.

• Hit the Enter key.

• Finally, copy the method on every cell for their prior sheet like sheet Day 3.

📕 Read More: 8 Ways to Calculate Age Using Formula on a Specific Date in Excel

Here, you will be finding one formula for all your current sheets, based on what they are named. You will sequentially merge the DATE, REPLACE, CELL, and SEARCH functions. For the image, you will find the sheet names 1, 2, and 3.

⬇️⬇️ STEPS ⬇️⬇️

• Initially, select the first sheet 1.
• Now, write the following formula on cell B6.

• Hit the Enter key.

• Next, copy the formula to every sheet for their respective date cells.

📕 Read More: 5 Ways to Use Formula to Find Next Month in Excel

This method also relies on the increment of the previous sheet. But, unlike the first method, it doesn’t need to get edited on every sheet. Rather sheet copying will automatically create sequential dates sheet by sheet. For the process, you should use a User Defined Function (UDF). In the image, UDF PrevSheet, two sheets named 1st Day, 2nd Day, and 3rd Day will show the steps of the method.

⬇️⬇️ STEPS ⬇️⬇️

• First, enter the starting date on the 1st Day sheet.

• After that, select Alt+F11 to open the Visual Basic Application (VBA).
• Now, select the Module option from the Insert You can also press Alt+I+L for the action.
• Next, write the following code on the Module, which is available in the Insert tab.

Function PrevSheet(Rg As Range)
Dim X As Variant
With Application.Caller.Parent
X = .Index
Do
If X = 1 Then
PrevSheet = CVErr(xlErrRef)
Exit Do
ElseIf TypeName(.Parent.Sheets(X - 1)) <> "Chart" And _
.Parent.Sheets(X - 1).Visible = xlSheetVisible Then
PrevSheet = .Parent.Sheets(X - 1).Range(Rg.Address).Value
Exit Do
End If
X = X - 1
Loop
End With
End Function

• Eventually, you should see the window like the image below.

• Now, on the sheet 2nd Day, click on B6.
• Here, write the following formula on the formula bar.

• Hit the Enter key.

• Copy the sheet to the next date sheets and sequential dates will be automatically updated.

Using VBA, you can employ Macro code to apply on all the existing worksheets. Moreover, this is the only process that will ensure all your cells are under the same date sequence without individual cell formula editing.

⬇️⬇️ STEPS ⬇️⬇️

• Initially, select Alt+F11 to open Visual Basic Application (VBA).
• Now, select the Module option from the Insert You can also press Alt+I+L for the action.
• Next, write the following code on the Module option.

Sub SEQUENTIAL_DATES_Across_Sheets()
Dim mainBook As Workbook
Set mainBook = ActiveWorkbook
Dim outx As Variant
Dim K As Variant
K = mainBook.Sheets.Count - 1
ReDim outx(K)
Dim j As Variant
For j = 0 To K
outx(j) = mainBook.Sheets(j + 1).Name
Next j
Initial_Date = InputBox("Enter the First Date: ")
IncrementX = CInt(InputBox("Enter the Increment: "))
Dim CountX As Integer
CountX = 0
Sheets(outx(0)).Range(Selection(1).Address).Formula = First_Date
Dim m As Integer
Dim n As Variant
For m = 0 To UBound(outx)
For Each n In Selection
Sheets(outx(m)).Range(n.Address) = Sheets(outx(0)).Range(Selection(1).Address).Value + IncrementX * Count
CountX = CountX + 1
Next n
Next m
End Sub

• You should see the window like the image below.

• Now, go back to the workbook window and choose cell B6.
• Press Alt+F8 or choose the Macros option from the Developer tab.
• In this stage, the Macro window will pop up to confirm the macro. Select Run.

• After that, you should enter the initial date in “Year, Month, Day” format, with parentheses of the DATE function. The image calls a date, which is the 1st of September, 2022.
• You can use the following formula.

• Click OK.

• Choose increments of the dates. For the image below 1 is entered.

• All your worksheets under the same cell will have this code implementation and consecutively show dates as per command. The image below shows the first date.

• Eventually, 4 days apart on sheet Sept 4 will have the date that is 4 times incremented from the starting date.

📕 Read More: How to Calculate Average Tenure of Employees in Excel

How to Insert Dates in Excel Automatically

We can insert dates automatically in many ways using different functions. Amidst these, the article will discuss the use of the NOW function.

⬇️⬇️ STEPS ⬇️⬇️

• Initially, select cell B6 to enter a date.
• Write the following formula on the cell.

• Hit the Enter key.
• Therefore, the running date will be visible to you.

📄 Important Notes

🖊️  You should format the cells in Date beforehand carefully.

🖊️  The NOW function doesn’t get updated as the date changes automatically. It will need to be manually added every time.

🖊️  You need to enable Macro before trying method 3 and method 4.

📝 Takeaways from This Article

The article allows the readers to understand the variety of options available to create sequential dates across Excel sheets.

📌  Using increment over sheet cells for another cell is an easy method. It needs copying and sheet names must be edited according to the source sheet.

📌  A combination of DATE, REPLACE, CELL, and SEARCH functions can be used. Not only can they confirm the date format, but also can be used for any cell with numerical values without edit.

📌  UDF declared function PrevSheet needs to be embedded onto the VBA first. The function can be used for any sheet without editing as well.

📌  A particular Macro declaration will add workbook(s) with sequential dates automatically. You can choose starting date and increment here as well.

Conclusion

To add sequential dates across Excel sheets, sheet increments, a combination of DATE, REPLACE, CELL, and SEARCH functions, UDF PrevSheet, and Macro-Enabled Workbook can be available ways as per the article. In case of further queries, please leave a comment for the author. The author will try their best to come up with a suitable solution. Follow ExcelDen to get more access to solutions regarding your Excel problems.

Related Articles

• 4 Examples to Calculate Dividend Growth Rate in Excel
• How to Calculate Overdue Days in Excel
• 5 Quick Ways to Use Excel Formula to Count Days from Date