Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Autofill Dates in Excel (3 Suitable Methods)

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. If you are also curious about it, download our practice workbook and follow us.


Download Practice Workbook

Download this practice workbook for practice while you are reading this article.


3 Easy Methods to Autofill Dates in Excel 

Here we’ve got a data set with the Names of Some Candidates for an interview in a company. Our objective today is to AutoFill their interview dates. The name of the employee is in column B and the dates will be in column C.

Note

All the operations of this article are accomplished by using Microsoft Office 365 application.


1. Dragging AutoFill Handle Icon

We can AutoFill dates by first entering one date, then dragging the Fill Handle to fill the rest of the cells. The steps of this method are given below:

📌 Steps:

  • First of all, select the first cell C5.
  • Now, write down a date in your Excel date format. We write our date in the MM/DD/YYYY format which is 12/07/2022.

Write down the first date manually to autofill dates

  • Then, drag the AutoFill Handle to fill in the rest of the dates.
  • After filling in 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.

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

Using Fill Weekdays option to autofill dates with weekdays

  • You will see that all the cells will fill with dates.

Dragging AutoFill Handle Icon to Autofill Dates

Thus, we can say that our procedure works perfectly, and we are able to AutoFill dates in Excel.

Note

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, 12/07/2022 and 12/10/2023. Then select the two cells together and drag the AutoFill Handle. 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)


2. Applying Fill Tool from Excel Toolbar

You can also autofill the cells with dates by the Fill tool of the Excel Toolbar. The procedure of this approach is explained below step-by-step:

📌 Steps:

  • At first, select the first cell C5.
  • Now, fill the first cell in any conventional way mentioned above.

  • Select the range of cells C5:C14.
  • Then, in the Home tab, click on the drop-down arrow of the Fill command and choose the Series option in Excel Toolbar under the Editing section.

Launching Series dialog box to autofill dates

  • As a result, you will get the Series dialog box.
  • In the dialogue box, under the Series in option, select Columns.
  • After that, under the Type option, select Date.
  • Next, in the Step value box, enter the increment you want for the increasing dates. (I am entering 3 for this example)
  • Thus, under the Date unit option, select by which you want the increasing dates, DayWeekday, Month, or Year (I am selecting Weekday).
  • So, the dialogue box looks like this:
  • Finally, click OK.

Setting suitable options to autofill dates

  • You will find the cells filled with increasing weekdays by step 3 and we are able to perform this without dragging.

Applying Fill Tool from Excel Toolbar

Hence, we can say that our approach works precisely, and we are able to AutoFill dates in Excel.

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


Similar Readings


3. Using Formula to Autofill Dates

Here, we will learn two different ways to AutoFill cells with dates in Excel. But if you don’t want to fill the cells with increasing dates, but rather with random dates within a given duration, you can use Excel functions to achieve this.


3.1 Autofill Dates with Repetition

To fill the cells with random dates between December 7, 2022, and December 27, 2022, with repetition, you can use the RANDBETWEEN and DATE functions of Excel to achieve this. The steps of this process are given as follows:

📌 Steps:

  • First, select the first cell C5.
  • Now, enter this formula in the cell:

=RANDBETWEEN(DATE(2020,12,7),DATE(2020,12,27))

  • Press the Enter.

Using RANDBETWEEN and DATE functions to autofill dates

  • It will return a random date between December 7, 2022, and December 27, 2022.
  • Then, double-click or drag the Fill Handle to fill the rest of the cells.
  • As you can see, we have filled the cells with random dates between December 7, 2022, and December 27, 2022. But one date has been repeated, like 12/17/2022 and 12/15/2022.

  • You get dates for everyone.

Autofill Dates with Repetition

Therefore, we can say that our formula works effectively, and we are able to AutoFill dates with repetition in Excel for our dataset.

Note

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


3.2 Autofill Dates without Repetition

To autofill the cells with random dates between December 7, 2022, and December 27, 2022, but without repetition, you can use this procedure. To complete the method, we will use the RANDBETEWEEN, DATE, INDEX, FILTER, COUNTIF, SEQUENCE, and ROWS functions. The steps are shown as follows:

📌 Steps:

  • Firstly, select the first cell C5.
  • After that, enter this formula in the first cell:

=RANDBETWEEN(DATE(2020,12,7),DATE(2020,12,27))

  • Now, press Enter.

Applying RANDBETEEN and DATE functions to get 1st date

  • It will return a random date between December 7, 2022, and December 27, 2022.
  • Then, enter this complex formula in the next cell C6:

=INDEX(FILTER(DATE(2022,12,SEQUENCE(DATE(2022,12,27)-DATE(2022,12,07),1,7,1)),COUNTIF($C$5:C5,DATE(2022,12,SEQUENCE(DATE(2022,12,27)-DATE(2022,12,7),1,7,1)))=0),RANDBETWEEN(1,ROWS(FILTER(DATE(2022,12,SEQUENCE(DATE(2022,12,27)-DATE(2022,12,7),1,7,1)),COUNTIF($C$5:C5,DATE(2022,12,SEQUENCE(DATE(2022,12,27)-DATE(2022,12,7),1,7,1)))=0))),1)

Formula Explanation

Within the SEQUENCE functions, DATE(2022,12,27)-DATE(2022,12,7) indicates the last day – the first day of the duration. You use your one.

Also within the SEQUENCE functions, 7 is the day number of the starting date (December 7). You use your one.

Within the DATE functions, 2022 and 12 are the year and the month of my starting date (December 7, 2022). You use your one.

Within the COUNTIF functions, $C$5:C5 is my first cell of the range. You use your one.

  • Again, press Enter.

Using a combine formula to autofill dates without repetition

  • Keep the rest of the formula unchanged. Then, double-click on or drag the Fill Handle icon.

  • You will find the rest of the cells filled with random dates between December 7, 2022, and December 27, 2022, without any repetition.

Autofill Dates without Repetition

Finally, we can say that our formula works successfully, and we are able to autofill dates without any repetition in our Excel spreadsheet.

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


Conclusion

That’s the end of this article. I hope that this article will be helpful for you and you will be able to autofill dates in Excel. Please share any further queries or recommendations with us in the comments section below if you have any further questions or recommendations.

Don’t forget to check our website, ExcelDemy, for several Excel-related problems and solutions. Keep learning new methods and keep growing!


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