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.
How to Autofill Dates in Excel: 3 Suitable MethodsÂ
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.
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.
- 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. To fill the cells with increasing years, select Fill Years.
- Here I am selecting Fill Weekdays to fill the cells with increasing weekdays.
- You will see that all the cells will fill with dates.
Thus, we can say that our procedure works perfectly, and we are able to 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, 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 Create Automatic Rolling Months in Excel
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 the Excel Toolbar under the Editing section.
- 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, Day, Weekday, Month, or Year (I am selecting Weekday).
- So, the dialogue box looks like this:
- Finally, click OK.
- You will find the cells filled with increasing weekdays by step 3 and we are able to autofill without dragging.
Hence, we can say that our approach works precisely, and we are able to AutoFill dates in Excel.
Read More: How to AutoFill Months in Excel
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.
- 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.
Therefore, we can say that our formula works effectively, and we are able to AutoFill dates with repetition in Excel for our dataset.
Don’t use any date in a custom way within a formula. Use the DATE function instead.
Read More:Â How to Copy Same Date in Excel
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.
- 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)
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.
- 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.
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 Increment Month by 1 in Excel
Download Practice Workbook
Download this practice workbook for practice while you are reading this article.
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.
AutoFill Dates: Knowledge Hub
- How to Enter Sequential Dates Across Multiple Sheets in Excel
- How to Autofill Dates in Excel Without Dragging
- How to Autofill Days of Week Based on Date in Excel
<< Go Back to Excel AutoFill | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
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.