How To Autofill Dates in ExcelAutofill Months and Years
If you need to enter a number of sequential dates in Excel in one column, you will not need to enter them all manually.
Instead, Excel has a feature that allows you to fill in your column with these dates, whether they are full dates or just years or months.
There is also a sequence function that will generate dates.
How To Autofill Sequential Dates
The quickest way to enter a series of sequential dates in a column is likely to be using the Fill handle option.
To do this, first, enter the initial date and make sure you have it formatted as a normal date.
As an example, suppose you put the date 10/15/2022 in cell A2.
Then, suppose you wanted to finish populating the column with the dates for the rest of the month.
- You would select A2, the first date that you entered.
- Then, you would place the cursor in the cell’s lower right corner, and when you see the cross, which is the fill handle, you will drag this down the column until you reach the final cell you wish to populate, and Excel will populate these cells with the remaining dates in the month.
- After you do this, you should have the dates from 10/15/2022 through 10/31/2022 in cells A2:A18.
Another method you could use to autofill sequential dates is to use the right mouse button instead of the left mouse button and then choose Fill Days when a menu pops up.
How To Autofill Months
You can fill in dates in a similar manner.
As an example, suppose the first date we have is 6/15/2021.
You would then have 7/15/2021, 8/15/2021, 9/15/2021, etc.
As you can see, the month will change in sequence, but the day will not change.
Here is an example using the starting date from above, 6/15/2021, and Excel autofill 12 months.
- First, enter the initial date in cell A2. Be sure to enter it in date format rather than text format; otherwise, Excel will not recognize it as a date.
- Then, we will select the cell we entered the date in and place your cursor in the cell in the lower right corner until you see a small black cross. This is the fill handle, and it can be used to autofill data.
- Next, select and drag this fill handle using the right mouse button into the cell that you wish the series of dates to end in. For this example, you wish to have Excel autofill 12 months starting with 6/15/21, so the last date would be 5/15/2022. This means you will place the fill handle in cell A13. While you are moving the fill handle, there will be a sequence of dates showing in the tooltip. These dates are not a problem and will be changed to months next.
- The default is Excel has dates be populated sequentially rather than months. However, using the right mouse button will bring up Auto Fill Options in the lower right corner of the selection. When a drop-down box appears, select Fill Months.
This will list the months sequentially from cells A2 to A13, 6/15/2021- 5/15/2022.
How to Autofill Years in Excel
You can also use autofill to fill in years or weekdays in a list.
The autofill option will list sequential years based on the initial date you enter. Here is how to do this.
First, you enter the date for which you want a list of dates in sequential years generated.
For this example, you want to generate the dates for ten years starting with 6/15/2021. So, you enter this date in Cell A2.
Then, you select cell A2 and place your cursor in the lower right corner of the cell.
Once you see the fill handle, you drag it and place it in the last cell for the chosen series of years.
So, in this example, that would be A11 since you want ten years starting with 6/15/2021. As you drag the fill handle, you should see sequential dates in the appropriate cell.
Once the sequential dates appear in the cells, you can change them to years as you want.
To do this, click the icon for the Auto Fill Options in the selection box and select Fill years in the drop-down box.
Now, the cells from A2 to A11 should be populated with the years from 6/15/2021 to 5/15/2030.
How To Autofill Weekdays
The process for having Excel autofill weekdays is very similar to having it autofill years.
You simply enter the initial date you want in a cell, for example, Cell A2.
Then, place the mouse over the lower right corner of the cell until the fill handle appears.
Next, drag the fill handle into the cell where your last date will be. Then, when the dates appear in the cells, you can change them as you did for years, except choose Fill Weekdays.
How To Create a Series of Dates Using the Sequence Function
The function Sequence in Excel can return a sequence of numbers based on the values you specify.
But, since Excel stores dates in the same way as numbers, you can use it to make a list of dates.
This function will let you make a list of numbers in a particular range.
The parameter of this range is = SEQUENCE(rows, [columns], [start], [step]) with:
- Rows = the number of rows that are to be populated
- Columns(optional) = the number of columns to be filled
- Start = the initial value in the list
- Step = a step for incrementing a list of numbers
As an example, suppose you would like to create a list of 15 dates in column A, and you want it to start with 6/15/2021.
Start by entering the formula =SEQUENCE(10,1,E1,1) in A2.
Then, because you want to make a list of subsequent days, you need to use a step parameter of one. If you wanted to have alternative dates, you could use two.
If you end up with numeric values instead of dates, you can change this by selecting the data and changing the formatting, so dates will show. This can be done by going to the home tab and selecting this option from the drop-down box.
For the example above, E1 was the cell reference for obtaining the date for the formula. However, you could instead use the date formula and then enter the specific values.
As an example, the following formula could be used, and you would obtain the same results if the correct date is used in the formula.
The above formula is an array formula, and the values calculated would be spilled into additional cells.
In the example we used, the formula was entered into cell A2, and the results went from cell A3 to A16.
If some of these cells had data already in them, this formula would have this error: #SPILL.
Here is an explanation of this function.
If you selected all of the dates included in your list (A2:A11) and chose the format to be general, you would get a list of numbers instead of dates.
This would occur because, as we discussed earlier, dates in Excel are stored as numbers.
If you wanted to, you could also make a list of dates with other steps, say every six days. To do this, you would set the step as six instead of 1.
In this article, we explained a few different ways to autofill dates in Excel.
Using any of these methods can save you time over entering a large number of dates manually.