# Continuous month count formula in Excel

Asked by At

I want to make continues month list starting from specific date in English. Lets say my date is `29.4.2019`. I would like Excel formula to display months in English starting from that date, so my list would look like (by dropping down):

``````May
July
August
September
October
November
December
January
February
etc...
``````

I have tried this formula but it ends up on December, also I have to input `+1, +2, +3` manually:

``````=CHOOSE(MONTH(A1)+1;"January";"February";"March";"April";"May";"July";"August";"September";"October";"November";"December")
``````

Also the problem is that I use Finnish Excel so function should be `=TEXT(MONTH(A1);"kkkk")` instead of `=TEXT(MONTH(A1);"mmmm")` if I want to use more simple version with `=TEXT` function. However this will not work once Excel will be opened on English version of Excel as it will not recognize "kkkk" inside the formula and will give an error.

I have VBA to convert to international format but this does not seem to work either (https://superuser.com/questions/730371).

``````Public Function FMT\$(ByVal Value, ByVal strFormat)
FMT = VBA.Format\$(Value, strFormat)
End Function
``````

EDIT:

Ok. That was stupid mistake... I skipped "June" in my list. This function seems to be working now:

``````=CHOOSE(MONTH(A1)+1;"January";"February";"March";"April";"May";"June";"July";"August";"September";"October";"November";"December")
``````

## 2 Answers On Best Solutions

I guess you could try the following:

``````=IF(C1="";TEXT(\$A\$1;"kkkk");TEXT(DATE(YEAR(\$A\$1);1+MONTH("1-"&C1&"-"&YEAR(\$A\$1));1);"kkkk"))
`````` The above formula basically uses two branches of the `IF` (you might have to pick a more appropriate condition depending on the layout of your excel sheet and where you need the results to be, but without knowing that, that's the best I can offer).

So basically if it's the first one, the cell directly above will be blank, so the formula will be simply `TEXT(\$A\$1,"kkkk")`. If there's a value, then we need to add one month, and to do that as accurate as possible, I would use `DATE(YEAR(\$A\$1),1+MONTH("1-"&C1&"-"&YEAR(\$A\$1)),1)`, which basically takes the year of the date supplied in A1, the month following the previous month, and 1 as the date. Excel can understand the format `d-mmmm-yyyy` as date, so I'm taking advantage of that.

Not using `TEXT`:

``````=CHOOSE(IF(C1="";MONTH(\$A\$1);MOD(MONTH("1-"&C1&"-"&YEAR(\$A\$1));12)+1);"January";"February";"March";"April";"May";"June";"July";"August";"September";"October";"November";"December")
`````` On

With the smallest possible change from how your own code works:

``````=CHOOSE(MOD(MONTH(\$A\$1)+Row()-2, 12)+1, "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December")
``````

First, the `+Row()-2` means that you no longer need to type the `+1`, `+2`, `+3` manually - it will use the Row number that the cell is on. (Depending on which row your list starts on, you may need to adjust the `-2`)

Next, we use `Mod(<adjusted month>,12)` - this means, every time we reach 12, it resets back to 0: `1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 0, 1, 2, 3, 4, ...`

Finally, add 1.

If we start in December, and the formula is in row 2 then we get `MOD(Month("2018-12-01")+Row()-2, 12)+1`, which becomes `MOD(12 + 2 - 2, 12) + 1` or `MOD(12, 12) + 1` and finally `0 + 1`.
The next item will be on Row 3, so `MOD(Month("2018-12-01")+Row()-2, 12)+1` becomes `MOD(12 + 3 - 2, 12) + 1` or `MOD(13, 12) + 1` and finally `1 + 1` for February.