How to Autofill Dates in Excel (3 Suitable Methods)

Today I will be showing how to autofill dates in Excel.

While working in Excel, many a time we are to work with dates. We are to insert dates in a single cell or a range of cells.

Today I will be showing how you can autofill a range of cells with dates in Excel.


Download Practice Workbook


How to Autofill Dates in Excel (3 Ways)

Here we’ve got a data set with the Names of Some Candidates for an interview in Johnson Group.

Data Set to Autofill Dates in Excel

Our objective today is to autofill their interview dates.


Method 1: Autofill Dates in Excel by Dragging the Fill Handle

We can autofill dates by first entering one date, then dragging the Fill Handle to fill the rest of the cells.

  • Select the first cell and conventionally enter any date, like MM/DD/YYYY.

Or you can use the DATE function of Excel:

=DATE(year,month,day)

Or if you want to enter today’s date, you can use the TODAY function of Excel:

=TODAY()

Here I am entering a date in a conventional way, 10/20/2021. This is October 10, 2021.

Entering the First Date to Autofill Dates in Excel

After filling all the cells, you will find a small square box in the right bottom corner. Click the drop-down menu associated with it.

  • You will see a handful of options like this.

Options with the Fill Handle to Autofill Dates in Excel

  • To fill the cells with increasing dates, select Fill Series or Fill Days.
  • Or to fill the cells with increasing weekdays, select Fill Weekdays.
  • To fill the cells with increasing months, select Fill Months.
  • And to fill the cells with increasing years, select Fill Years.
  • Here I am selecting Fill Weekdays to fill the cells with increasing weekdays.

Selecting an Option from the Fill Handle Autofill Dates in Excel

Note: The step increment in this method is 1. If you want any other increment but 1, then fill the first two cells with the required increment, then execute the steps.

For example, to fill the cells with dates increasing by 3 in each step, enter the first two dates, 10/20/2021 and 10/23/2021.

Then select the two cells together and drag the Fill Handle.

Dragging the Fill Handle to Autofill Dates in Excel

As you can see, we have got a series of dates with increment 3.

Read More: How to Autofill Numbers in Excel without Dragging (5 Quick Methods)


Method 2: Autofill Dates in Excel by the Fill Tool from Excel Toolbar

You can also autofill the cells with dates by the Fill tool of Excel Toolbar.

  • Fill the first cell in any conventional way mentioned above.

Entering the First Date to Autofill Dates in Excel

  • Then select all the cells (including the filled cell) and go to the Home>Fill>Series option in Excel Toolbar under the section Editing.

  • Click on Series. You will get the Series dialogue box.

Series Dialogue Box to Autofill Dates in Excel

  • In the dialogue box, under the Series in option, select Columns.

Under the Type option, select Date.

In the Step value box, enter the increment you want for the increasing dates. (I am  entering 3 for this example)

Then under the Date unit option, select by which you want the increasing dates, Day. Weekday, Month, or Year (I am selecting Weekday).

So the dialogue box looks like this:

Series Dialogue Box to Autofill Dates in Excel

  • Finally, click OK. You will find the cells filled with increasing weekdays by step 3.

Read More: How to Perform Predictive Autofill in Excel (5 Methods)


Similar Readings


Method 3: Autofill Dates in Excel by Using Formula

We have learned two different ways to autofill cells with dates in Excel.

But if you don’t want to fill the cells with increasing dates, rather with random dates within a given duration, you can use Excel functions to achieve this.


Case1: Autofill Dates with Repetition

To fill the cells with random dates between October 20, 2021, and November 20, 2021, with repetition, you can use the RANDBETWEEN function of Excel to achieve this.

Enter this formula in the first cell:

=RANDBETWEEN(DATE(2020,10,20),DATE(2020,11,20))

It will return a random date between October 20, 2021, and November 20, 2021.

[Don’t use any date in a custom way within a formula. Use the DATE function instead.]

Excel RANDBETWEEN Function to Autofill Dates in Excel

Then double click or drag the Fill Handle to fill the rest of the cells.

Dragging the Fill Handle to Autofill Dates in Excel

As you can see, we have filled the cells with random dates between October 20, 2021, and November 20, 2021.

But some dates have been repeated, like 11/19/2020, 10/23/2020, 11/9/2020, etc.


Case 2: Autofill Dates without Repetition

To autofill the cells with random dates between October 20, 2021, and November 20, 2021, but without repetition, you can use this procedure.

Enter this formula in the first cell:

=RANDBETWEEN(DATE(2020,10,20),DATE(2020,11,20))

It will return a random date between October 20, 2021, and November 20, 2021.

Then enter this complex formula in the next cell:

=INDEX(FILTER(DATE(2021,10,SEQUENCE(DATE(2021,11,20)-DATE(2021,10,20),1,20,1)),COUNTIF($C$4:C4,DATE(2021,10,SEQUENCE(DATE(2021,11,20)-DATE(2021,10,20),1,20,1)))=0),RANDBETWEEN(1,ROWS(FILTER(DATE(2021,10,SEQUENCE(DATE(2021,11,20)-DATE(2021,10,20),1,20,1)),COUNTIF($C$4:C4,DATE(2021,10,SEQUENCE(DATE(2021,11,20)-DATE(2021,10,20),1,20,1)))=0))),1)

Complex Formula to Autofill Dates in Excel

  • Within the SEQUENCE functions, DATE(2021,11,20)-DATE(2021,10,20) indicates the last day – the first day of the duration. You use your one.
  • Also within the SEQUENCE functions, 20 is the day number of the starting date (October 20). You use your one.
  • Within the DATE functions, 2021 and 10 are the year and the month of my starting date (October 20, 2021). You use your one.
  • Within the COUNTIF functions, $C$4:C4 is my first cell of the range. You use your one.

Keep the rest of the formula unchanged. Then double click on or drag the Fill Handle.

You will find the rest of the cells filled with random dates between October 20, 2021, and November 20, 2021, without any repetition.

Read More: How to Use Autofill Formula in Excel (6 Ways)


Conclusion

Using these methods you can autofill any range of cells with dates in Excel, in your desired way. Do you have any questions? Feel free to ask us.


Related Articles

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.

2 Comments
  1. I want to enter a date in excel for a work schedule. Then the days of the week will auto populate based on the date entered.

    WORK WEEK ENDING [J:1] 3/17/2022
    F:6,H,J,L,N,P,R would auto populate with R:6 matching J:1.
    Thanks for any ideas.

    • Hi SteelWolf,

      I am not sure what you meant exactly by that. But assuming you want to put in a date in cells F6, H6, J6, L6, N6, P6, and R6 that match with cell J1, you can use formulas in those cells with reference to cell J1. For example, let’s assume you want the previous date, exact date, and the following date of cell J1 in cells F6, H6, and J6.
      You can write =J6 in cell H6. Write =EDATE(J6,-1) in cell F6 and =EDATE(J6,1) in J6.

      You can modify/ use different formula according to your needs in this way.
      But if you want you use the “autofill” in those scattering cells, it is not possible yet as of the latest version of Excel.

      Hope that helps. If you meant something else entirely, please let us know.

Leave a reply

ExcelDemy
Logo