Quick, clean, and to the point

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 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 Jan 1 2015. First enter the start date. Next, add a formula the starts off 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.

In this case, I simply need to add 1 to the month. Now when I copy the value down, the dates change by one month. Notice that Excel takes care of 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 are free to hard code any value you like. For example, I can easily hard code the month 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, using the TIME function along with the HOUR, MINUTE, and SECOND functions.

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

To generate times separated by 15 minutes, I add 15 to the minute component.


Related shortcuts

Dave Bruns

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.