+ Start a Discussion
Adi VaxmanAdi Vaxman 

How to create a date formula that adds number of months and displays result as text

Our agreements usually have a 12 months term, and in order to forecase correctly I need to create custom fields for each of these 12 months post closing.

For example, a deal closes on September 2016, I would like the fields to return a result as follows:
Month 1 - September 2016
Month 2 - October 2016
Month 3 - November 2016
Month 4 - December 2016
Month 5 - January 2017
Month 6 - February 2017

and so on.

I am using the following formula to display month 1 and month 2 and it is working well - however, it does not take into account the year changing once we reach December, and I have no idea how to make it do that:

For Month 1 I am using
CASE(MONTH(CloseDate)+1), 1, "January", 2, "February", 3, "March", 4, "April", 5, "May", 6, "June", 7, "July", 8, "August", 9, "September", 10, "October", 11, "November", 12, "December", "None") & " " & TEXT(YEAR(CloseDate))

For Month 2 I am using
CASE(MONTH(CloseDate)+1, 1, "January", 2, "February", 3, "March", 4, "April", 5, "May", 6, "June", 7, "July", 8, "August", 9, "September", 10, "October", 11, "November", 12, "December", "None") & " " & TEXT(YEAR(CloseDate))

How do I add to this a calculation of the year, so that when 2016 is over, the formula will know to display the following year?

thank you!
CillaCilla
Could you approximate 1 month = 30 days since we are not interested in the exact date, just to capture the year rolling over from month-to-month. Then, add 30 (60, 90, 120, etc) days to close date, and use that year. 

MONTH 1
CASE( .... & TEXT(YEAR(CloseDate)) )

MONTH 2
CASE( ... & TEXT(YEAR(CloseDate + 30)) )
Adi VaxmanAdi Vaxman
That sounds good, I will try it and let you know - thanks!