To solve this formula, Excel first extracts the year, month, and day values from the date in B6, then adds 1 to the month value. Next, a new date is assembled by the DATE function, using the same day and year, and month + 1 for month. The first formula therefore returns a new date of 2/15/2010, one month later than the starting date. Once the first formula is entered, it is copied down as far as needed. Each subsequent formula creates a new date incremented by one day. Note: if you start with a date like January 31, the formula above will skip February and move on to March. This happens because the new date, 2/31/2010 doesn’t exist, so Excel uses the day value to roll forward to 3/3/2010, 3 days after the last day in February. If you need a series of dates where every date is the last day of the month you can use EODATE like this:
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
