How to create date and time series with formulas

Although you can use Excel's AutoFill feature to fill in a series of dates and times, you can also do the same thing with formulas. The advantage of using a formula is that you can easily change the starting value and generate a new series.

Let's take a look.

Often you'll need to generate a series of dates separated by a certain interval of days, months, or years. You can easily do this with Excel's Date functions.

For example, assume you want a series of dates separated by one month, starting from January 1, 2015. First, enter the start date. Next, add a formula that begins with the DATE function.

For each argument, use the corresponding function to extract the value you need from the start date. So, for example, I can use YEAR to get the "year" value from B6, MONTH to get the "month" value, and DAY to get the "day" value. When I copy that formula down, I get the same date in every cell, because I haven't added any change value yet.

Since I want to change the date by one month, I simply need to add "1" to the month. Now when I copy that formula down, the dates change by one month. Notice that Excel takes care of changing the year value for me.

For the next example, I'll follow the same process. But this time, I'll set the year to increase by one.

You can also easily step back in time. Just subtract values instead of adding values.

Note that all values are fully dynamic and will recalculate when you supply a new start value.

Also note that you're free to hard code any value you like. For example, I can easily hard code the day value in the first example to be "15" and generate a series of dates that are one month apart, always on the 15th of the month.

You can do the same thing with time values by using the TIME function along with the HOUR, MINUTE, and SECOND functions.

To generate times separated by one hour, I just need to add "1" to the Hour component.

To generate times separated by 15 minutes, I need to add "15" to the Minute component.