Google-sheets – How to make sequential list of months

formulasgoogle sheetsgoogle-sheets-arrayformulagoogle-sheets-dates

I want to fill column names with a sequential list of the last day of each month. For example, 3/31/2018, 4/30/2018, 5/31/2018, etc. Is there a formula that can do this? If cell B1 = 3/31/2018, I tried this:

=DATE(YEAR(B1), MONTH(B1)+1, DAY(B1))

But this produces 5/1/2018 since April has only 30 days.

Best Answer

An alternative solution, that looks similar to your original formula, is to use the following:

=date(year(B1),month(B1)+2,0)
Related Topic