function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
GrrrrrrrrrrrrrGrrrrrrrrrrrrr 

There is no error when I check syntax, but still not working

In my quest to find a way to report on rolling months in custom object, I decided to brainiack this approach.

I set a field to todays date.  Then I add a month.  So, this formula works somewhat (returns wrong month right now):

IF(MONTH(TODAY()) + 1 >= 12, "1", TEXT(MONTH(TODAY()) + 1)) & "/1/" & IF(MONTH(TODAY()) + 1 >= 12, TEXT(YEAR(TODAY()) +1), TEXT(YEAR(TODAY())))

 

But if I want to calculate the following months based on today (for example):

IF(MONTH(TODAY()) + 2 >= 12, "1", TEXT(MONTH(TODAY()) + 2)) & "/1/" & IF(MONTH(TODAY()) + 2 >= 12, TEXT(YEAR(TODAY()) +1), TEXT(YEAR(TODAY())))
IF(MONTH(TODAY()) + 3 >= 12, "1", TEXT(MONTH(TODAY()) + 3)) & "/1/" & IF(MONTH(TODAY()) + 3 >= 12, TEXT(YEAR(TODAY()) +1), TEXT(YEAR(TODAY())))
IF(MONTH(TODAY()) + 4 >= 12, "1", TEXT(MONTH(TODAY()) + 4)) & "/1/" & IF(MONTH(TODAY()) + 4 >= 12, TEXT(YEAR(TODAY()) +1), TEXT(YEAR(TODAY())))
IF(MONTH(TODAY()) + 5 >= 12, "1", TEXT(MONTH(TODAY()) + 5)) & "/1/" & IF(MONTH(TODAY()) + 5 >= 12, TEXT(YEAR(TODAY()) +1), TEXT(YEAR(TODAY())))
IF(MONTH(TODAY()) + 6 >= 12, "1", TEXT(MONTH(TODAY()) + 6)) & "/1/" & IF(MONTH(TODAY()) + 6 >= 12, TEXT(YEAR(TODAY()) +1), TEXT(YEAR(TODAY())))
IF(MONTH(TODAY()) + 7 >= 12, "1", TEXT(MONTH(TODAY()) + 7)) & "/1/" & IF(MONTH(TODAY()) + 7 >= 12, TEXT(YEAR(TODAY()) +1), TEXT(YEAR(TODAY())))

 these all return the same date (1/1/2013).

 

AND if I try to use the first rolling date calculated like this:

IF(MONTH(DATEVALUE(Rolling_Months_1__c)) + 2 >= 12, "1", TEXT(MONTH(TODAY()) + 2))  & "/1/" & IF(MONTH(DATEVALUE(Rolling_Months_1__c)) + 2 >= 12, TEXT(YEAR(TODAY()) +1), TEXT(YEAR(TODAY())))

 I do not get any syntax errors, BUT my field displays this -->   #error   <--  instead of a date.

 

I should point out that ALL my formula fields are set to return values as text.

 

Anyone have any ideas on this?

 

Thanks!

Temple

 

 

 

 

GrrrrrrrrrrrrrGrrrrrrrrrrrrr

Ok, I solved the first month problem by removing the >= and making it just >

 

IF(MONTH(TODAY()) + 1 > 12, "1", TEXT(MONTH(TODAY()) + 1)) & "/1/" & IF(MONTH(TODAY()) + 1 > 12, TEXT(YEAR(TODAY()) +1), TEXT(YEAR(TODAY())))

 Still cant figure out the datevalue / text conversion problem!!

RamitRamit

Hi,

 

There in no problem with the syntax so you are not getting any complilation error. But when the formula gets executed then it is getted invalid month whenever, the else part is getting executed for calculating Month.

Because you have put the conditional check on field "Rolling_Months_1__c" but in the else part you are using Today's Month and then adding 2 to it.

 

 

Please see the correction below.

 

IF(MONTH(DATEVALUE(Rolling_Months_1__c)) + 2 >= 12, "1", TEXT(MONTH(Rolling_Months_1__c) + 2))  & "/1/" & IF(MONTH(DATEVALUE(Rolling_Months_1__c)) + 2 >= 12, TEXT(YEAR(TODAY()) +1), TEXT(YEAR(TODAY())))