How to Enter Sequential Dates Across Multiple Sheets in Excel

Get FREE Advanced Excel Exercises with Solutions!

In this regard, When working with dates in Excel, we have learned to enter dates in our various desired orders. Besides, we have learned to enter a range of sequential dates. But, sometimes, it might be needed to enter sequential dates in multiple sheets according to their purposes. In this article, I will show you 2 suitable ways to enter sequential dates across multiple sheets in Excel.


Download Practice Workbook

You can download our practice workbook from here for free!


2 Suitable Ways to Enter Sequential Dates Across Multiple Sheets in Excel

1. Using VBA Macro to Enter Sequential Dates Across Multiple Sheets in Excel

We can enter sequential dates across multiple sheets by using a VBA macro in Excel. In this regard, we can accomplish this task for both single cell or a range of cells according to our requirements. We will show you both examples here to make your understanding clear.

In doing this, 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.


Example 1: Entering Sequential Dates in a Single Cell Across Sheets

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

Sample Dataset to Enter Sequential Dates Across Multiple Sheets in Excel

Our objective today is to enter sequential dates across the worksheets. For example, if the Date in Worksheet “Day 1” is 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.


📌 Step 1: Generating a Macro to Enter Sequential Dates

  • First and foremost, go to the Developer tab >> Visual Basic tool. Thus, you will go to the VB Editor. You can also use the Alt + F11 key on your keyboard to do this.

Access the Visual Basic Tool

  • Afterward, go to the Insert tool in the upper left corner.
  • Following, select Insert >> Module. A new module will be opened for writing VBA code.

Insert a Module to Insert VBA Code

  • At this time, insert the following VBA code and paste it in the created module.
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
  • Afterward, press Ctrl + S to save the Excel file with the VBA code.

VBA Code to Enter Sequential Dates Across Multiple Sheets in Excel

  • In this regard, you must save the file as .xlsm (Excel Macro-Enabled Workbook) file. Otherwise, the macro won’t work.

Save As Window


📌 Step 2: Entering Sequential Dates in Multiple Sheets Using That Macro

After generating the Macro, now, you will need to run that macro properly.

  • To do this, come back to any of your Excel Worksheets and select the cell where you want to enter the sequential dates. In this example, I am selecting cell C4.
  • Afterward, go to the Developer tab >> Macros tool. You can also press Alt + F8 on your keyboard to open the Macro window.

Access the Macros Tool to Enter Sequential Dates Across Multiple Sheets in Excel

  • As a result,  the Macro dialogue box containing all the Macros will be opened.
  • Following, select SEQUENTIAL_DATES macro and click on the Run button.

Run Macro to Enter Sequential Dates Across Multiple Sheets in Excel

  • At this time, 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 of Excel.
  • For the sake of this example, I am entering January 01, 2022, through the following formula and clicking on the OK button.
=DATE(2022,1,1)

Insert Formula to Enter First Date of Sequential Dates Across Multiple Sheets in Excel

  • Now, another Microsoft Excel 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 and then clicking on the OK button.

Insert Increment to Enter Sequential Dates Across Multiple Sheets in Excel

Thus, You will find a sequential date generated in the same cell of each worksheet, with a step increment of 2 days. And, the final outcome of the sheets would look like this.

Entered Sequential Dates Across Multiple Sheets in Excel


Example 2: Entering Sequential Dates in a Range of Cells Across Sheets

This code will be also useful if you want to enter sequential dates in a range of cells across worksheets. 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.

Sample Dataset to Enter Sequential Dates for a Range of Cells Across Multiple Sheets in Excel

Now, we want to enter sequential dates for all the interview dates of all the worksheets. To achieve this, follow the steps below.

📌 Steps:

  • First, repeat the step 1 procedures from the previous example to insert the VBA macro in the Excel file.
  • Next, select all the cells (C5:C8 here) in any worksheet >> go to the Developer tab >> Macros tool.

Access the Macros Tool

  • As a result, the Macro window will appear.
  • Following, choose the SEQUENTIAL_DATES macro and click on the Run button.

Run Macro to Enter Sequential Dates Across Multiple Sheets in Excel

  • As a result, a Microsoft Excel dialogue box will appear asking for the first date.
  • Following, insert the following formula in the input text box and click on the OK button to start the dates for January 1, 2022.
=DATE(2022,1,1)

Enter the First Date of Sequential Dates

  • At this time, another Microsoft Excel dialogue box will appear asking for the increment.
  • Subsequently, put 2 inside the text box and click on the OK button to get dates at the one-day interval.

Enter the Increment of Sequence

Thus, you will find all the selected cells from all the worksheets that have been filled with sequential dates, in your given increment. And, the final outcome would look like this.

Entered Sequential Dates Across Multiple Sheets in Excel

Read more: How to Autofill Dates in Excel


2. Enter Sequential Dates According to Sheet Names in Excel 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. 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, 2022. The sheet with the name “2” will have January 02, 2022. And so on.

To achieve this, we shall use a combination of the DATE, REPLACE, CELL, and SEARCH functions of Excel. Follow the steps below to do this.

📌 Steps:

  • First, go to each worksheet and enter this formula in place (cell C4 here) of the date.
=DATE(2022,1,REPLACE(CELL("filename",A1),1,SEARCH("]",CELL("filename",A1),1),""))

Formula to Enter Sequential Dates Across Multiple Sheets in Excel

  • Here, within the DATE function, 2022 and 1 are the years and months respectively (January 2022). You can use it according to your needs. The REPLACE function returns the sheet name and that is used as the day of the date.
  • Now, keep the rest of the formula unchanged, and insert it in all the worksheets.

Thus, you will be able to enter the sequential dates across sheets in Excel according to the sheets’ names. And, the final output should look like this.

Entered Sequential Dates Across Multiple Sheets in Excel with Formula


How to Autofill Dates in Excel in the Same Sheet Without Dragging

Now, sometimes, it might appear that you need to autofill dates in a single worksheet but without dragging the fill handle.

Say, you have a dataset with Student ID and their date of admission. Every student is admitted every day beginning from 1-Jan-2022. Now, you want to input only the beginning dates. But, you want to get all the other dates sequentially without dragging the fill handle.

Sample Dataset to Autofill Dates without Dragging

Follow the steps below to accomplish this.

📌 Steps:

  • First, click on the first input cell (Cell C5 here).
  • Afterward, go to the Data tab >> Data Tools group >> Flash Fill option.

Using Flash Fill Option

Thus, you will see that the dates will be automatically filled to the cells C6:C10 and the result should look like this.

Autofilled Dates without Dragging


Conclusion

So, in this article, I have shown you two suitable ways to enter sequential dates across sheets in Excel. You can also download our free workbook to practise. I hope you find this article helpful and informative. If you have any further queries or recommendations, please feel free to comment here.

And, visit ExcelDemy to learn more things about Excel! Have a nice day!


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 the 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.

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