You need to sign in to do that
Don't have an account?
Adi 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!
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!
MONTH 1
CASE( .... & TEXT(YEAR(CloseDate)) )
MONTH 2
CASE( ... & TEXT(YEAR(CloseDate + 30)) )