How to Auto Repeat Dates in Excel

In this article, you will learn how to auto repeat the dates in specific cells in a column by writing in two input boxes in Excel formula. This is useful when you want to create a list of dates that follow a certain pattern, such as repeating every other day, every week, or every month.

To auto repeat the dates in Excel, you need to use two input boxes: one for the starting date and one for the step value. The starting date is the first date that you want to appear in your list. The step value is the number of days that you want to add or subtract from the previous date to get the next date. For example, if you want to repeat the dates every other day, the step value is 2. If you want to repeat the dates every week, the step value is 7. If you want to repeat the dates every month, the step value is 30 or 31, depending on the month.

Procedures

To auto repeat the dates in Excel, follow these steps:

1. In a cell, enter the starting date that you want to use for your list. For example, enter 1/1/2024 in cell A1.
2. In another cell, enter the step value that you want to use for your list. For example, enter 2 in cell B1 if you want to repeat the dates every other day.
3. In the cell below the starting date, enter the formula =A1+$B$1, where A1 is the cell that contains the starting date and B1 is the cell that contains the step value. This formula adds the step value to the starting date and returns the next date in the sequence. For example, enter =A1+$B$1 in cell A2.
4. Select the cell that contains the formula and drag the fill handle down or to the right to fill the rest of the cells with the formula. This will create a list of dates that follow the pattern that you specified. For example, drag the fill handle from cell A2 to cell A10 to fill the cells with the formula.

Example

To illustrate how to auto repeat the dates in Excel, let’s use a scenario where you want to create a list of dates that repeat every week, starting from January 1, 2024. Here is how you can do it:

1. In cell A1, enter the starting date 1/1/2024.
2. In cell B1, enter the step value 7.
3. In cell A2, enter the formula =A1+$B$1.
4. Drag the fill handle from cell A2 to cell A10 to fill the cells with the formula.

The result will look something like this:

As you can see, the dates in the column repeat every week, as specified by the step value.

Other Approaches

There are other ways to auto repeat the dates in Excel, such as using the AutoFill feature or the SEQUENCE function. Here are some brief explanations of these methods:

• AutoFill: You can use the AutoFill feature to create a series of dates that increment by one day, one week, one month, or one year. To use this method, you need to enter the initial date in the first cell and then drag the fill handle across the adjacent cells that you want to fill with sequential dates. Then, you can choose the option that suits your needs from the AutoFill Options button, such as Fill Weekdays, Fill Months, or Fill Years. For more details, you can check out this article.
• SEQUENCE: You can use the SEQUENCE function to generate a list of dates with a formula. This function returns an array of sequential numbers that can be converted to dates by adding them to the starting date. To use this method, you need to enter the formula =start_date+SEQUENCE(rows,columns,0,step), where start_date is the first date that you want to use, rows is the number of rows that you want to fill, columns is the number of columns that you want to fill, 0 is the starting number for the sequence, and step is the step value that you want to use. For more details, you can check out this article.