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.
The Excel DATE function creates a valid date from individual year, month, and day components. The DATE function is useful for assembling dates that need to change dynamically based on other values in a worksheet.
The Excel YEAR function returns the year component of a date as a 4-digit number. You can use the YEAR function to extract a year number from a date into a cell or to extract and feed a year value into another formula, like the...
The Excel MONTH function extracts the month from a given date as number between 1 to 12. You can use the MONTH function to extract a month number from a date into a cell, or to feed a month number into another function like the...
The Excel DAY function returns the day of the month as a number between 1 to 31 from a given date. You can use the DAY function to extract a day number from a date into a cell. You can also use the DAY function to extract and feed a day value...
The Excel TIME function is a built-in function that allows you to create a time with individual hour, minute, and second components. The TIME function is useful when you want to assemble a proper time inside another formula.
The Excel HOUR function returns the hour component of a time as a number between 0-23. For example, with a time of 9:30 AM, HOUR will return 9. You can use the HOUR function to extract the hour into a cell, or feed the result into another formula...
The Excel MINUTE function extracts the minute component of a time as a number between 0-59. For example, with a time of 9:45 AM, minute will return 45. You can use the MINUTE function to extract the minute into a cell, or feed the result into...