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.


Approach

1. Using Sheet Increment

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.
=‘Day1’!B6+1

Previous sheet increment - sequential date across sheets

  • Hit the Enter key.

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

Copied to sheets - sequential date across sheets

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


approach

2. Combination of Multiple Functions

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.
=DATE(2022,3,REPLACE(CELL(“filename”,A1),1,SEARCH(“]”,CELL(“filename”,A1),1),””))

  • Hit the Enter key.

Combination of four functions - sequential date across sheets

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

🔨 Formula Breakdown

DATE(2022,3,REPLACE(CELL(“filename”,A1),1,SEARCH(“]”,CELL(“filename”,A1),1),””))

👉  Here, REPLACE returns the numerical sheet name and is then used as the day of the date.

👉  Finally, the DATE function holds 2022, 9 as the year 2022 and the 3rd month (March) Therefore, gets the output as “Day-Mar-22.

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


approach

3. Utilizing User-Defined Function

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.

UDF PrevSheet - sequential dates across sheets

  • Now, on the sheet 2nd Day, click on B6.
  • Here, write the following formula on the formula bar.
=PrevSheet(B6)+1
  • Hit the Enter key.

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

Copied function - across sheets to create sequential dates


approach

4. Employing VBA Code

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.

Macro use - sequential date across sheets

  • 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.
=DATE(2022,9,1)
  • Click OK.

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

increment - across sheets sequential dates

  • 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.
=NOW()
  • Hit the Enter key.
  • Therefore, the running date will be visible to you.

Now function - enter date automatically for sequential dates


📄 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

(Visited 125 times, 1 visits today)
Farihat

Farihat

An Excel enthusiast who wants to share and loves to solve Excel problems. I try to explore available options to guide readers to the maximum ways how they can solve. The world is limitless, and so are the problems in it. Let's help each other to grow. I would love to hear feedback and queries from readers. You can visit my LinkedIn for further information.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo