In this article, we are going to show you how to create a sequence of dates in Excel using the Auto Fill feature.
AutoFill is the action of dragging the small square at the bottom-right corner of a cell.
The small square is called the fill handle.
This feature can be used to create a sequence of dates as well as copying values or generating formulas. For detailed information, please see: With Autofill Excel Shows Its Smarts and Saves You Time
Create a sequence of weekdays, months or years
Using AutoFill with a date creates a sequence of days. But what about other intervals, like weekdays, months, or years?
To customize the Autofill options, either right-click on the square and drag the fill handle, or click the icon after dragging with left-click until you see the Autofill Options menu.
In this example, the options menu shows a section for date fields, because our cells contain date data. Select the option you need to update the filled cells.
Right-click
Left-click + AutoFill Options
Create a custom sequence of dates
If you need more options than the AutoFill Options menu has to offer, you can use the Series dialog instead. The Series dialog contains the same day, weekday, month, and year options, but also allows setting an interval other than 1.
To open the Series dialog, drag the fill handle by right-clicking and then selecting Series from the menu.
When you click OK, your range will be populated with dates, which will increase or decrease as much as the Step value and Date unit.
Be careful when using Stop value input, as you can actually set an end date and filling will stop when the value is reached. However, you need to know the date’s numeric value.
Excel keeps date and time values as numbers. Excel assumes that Jan 1st, 1900 is 1, and every subsequent date value is based on this. While whole numbers represent days, decimals represent time values.
For example, let’s say you want a sequence of dates which starts from 1/1/2021 and ends at 1/21/2021. The Stop value should be 44217.
Bonus: A quicker method to create a sequence of dates in Excel.
You can generate a sequence quickly by entering the first 2 dates of the sequence and using the AutoFill.
For example, assume that you need the last days of the months. Then, enter 1/31/2021 and 2/28/2021 into the first two cells. Next, all you need to do is to select both cells and drag the fill handle.
