How to Enter Sequential Dates Across Multiple Sheets in Excel

Sequential Dates of Multiple Cells Entered in Multiple Worksheets

Today I will be showing how to enter sequential dates across multiple sheets in Excel.

Up till now, we have learned to enter dates in Excel. We have also learned to enter a range of sequential dates in a single worksheet in Excel.

Today I will be showing how you can enter sequential dates in multiple sheets.


Download Practice Workbook


1. How to Enter Sequential Dates Across Multiple Sheets in Excel

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

First Worksheet to Enter Sequential Dates Across Multiple Worksheets

Second Worksheet to Enter Sequential Dates Across Multiple Worksheets

Third Worksheet to Enter Sequential Dates Across Multiple Worksheets

Fourth Worksheet to Enter Sequential Dates Across Multiple Worksheets

Fifth Worksheet to Enter Sequential Dates Across Multiple Worksheets

Our objective today is to enter sequential dates across the worksheets.

For example, if the Date in Worksheet “Day 1” 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.

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.


Step 1: Generating a Macro to Enter Sequential Dates

  • Press Alt + F11 on your keyboard. The VBA window will open.

VBA Window in Excel

  • Go to the Insert tool in the upper left corner. Select Insert>Module. A new module will be opened for writing VBA code.

Opening New Module in VBA Window

  • Copy the following VBA code and paste it there.

Code:

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

Macro Code in VBA Window to Generate Sequential Dates

  • Finally, save the file as Excel Macro-Enabled Workbook.

Saving Macro-Enabled Workbook


Step 2: Entering Sequential Dates in Multiple Sheets Using that Macro

After generating the Macro, come back to any of your Excel Worksheets.

Then select the cell where you want to enter the sequential dates.

In this example, I am selecting cell C4.

Selecting a Cell to Enter Sequential Dates

Then press Alt + F8 on your keyboard. A dialogue box containing all the Macros will be opened. Select SEQUENTIAL_DATES.

Macros Dialogue Box in Excel

Click on Run. You will find a prompt box asking you to enter the first date.

Enter the date that you want to insert in the first worksheet.

Enter it using the DATE function of Excel.

=DATE(year,month,day)

For the sake of this example, I am entering January 01, 2021.

=DATE(2021,1,1)

Input Box After Running Macro

Click on OK. Another 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.

Input Box After Running Macro

Click on OK. You will find a sequential date generated in the same cell of each worksheet, with a step increment of 2 days.

Sequential Dates Entered Across Multiple Worksheets in Excel

Sequential Dates Entered Across Multiple Worksheets in Excel

Sequential Dates Entered Across Multiple Worksheets in Excel

Sequential Dates Entered Across Multiple Worksheets in Excel

Sequential Dates Entered Across Multiple Worksheets in Excel

This code will be also useful if you want to enter multiple dates in a worksheet. 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.

Workbook with Multiple Worksheets to Enter Sequential Dates

We want to enter sequential dates for all the interview dates of all the worksheets.

To achieve this, select all the cells in any worksheet and repeat the above procedure.

Running Macro on a Range of Selected Cells

Input Box After Running Macro

Input Box After Running Macro

You will find all the selected cells from all the worksheets that have been filled with sequential dates, in your given increment.

Sequential Dates of Multiple Cells Entered in Multiple Worksheets

Read more: How to Autofill Dates in Excel


2. How to Enter Dates According to Sheet Names 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.

Workbook with Multiple Worksheets to Enter Sequential Dates

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, 2021.

The sheet with the name “2” will have January 02, 2020.

And so on.

To achieve this, we shall use a combination of the DATE, REPLACE, CELL, and SEARCH functions of Excel.

Go to each worksheet and enter this formula in place of the date:

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

Formula to Generate Sequential Dates Across Multiple Sheets

  • Here, within the DATE function, 2021 and 1 are the years and months respectively (January 2021). You use it according to your needs.
  • The REPLACE function returns the sheet name and that is used as the day of the date.

Keep the rest of the formula unchanged, and insert it in all the worksheets.

You will get the desired dates.

Sequential Dates Generated Using Formula

As you can see, we have inserted sequential dates in all the worksheets, according to the sheet names.


Conclusion

Using these methods, you can enter sequential dates across multiple worksheets in Excel pretty comfortably. Do you have any questions? Feel free to ask us.


Further Readings

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo