IF(Billing_Schedule_Code__c=1,
DATE(YEAR(today())+(IF(MONTH(today())>9,1,0)),
IF(MONTH(today())=1,4,
IF(MONTH(today())=2,4,
IF(MONTH(today())=3,4,
IF(MONTH(today())=4,7,
IF(MONTH(today())=5,7,
IF(MONTH(today())=6,7,
IF(MONTH(today())=7,10,
IF(MONTH(today())=8,10,
IF(MONTH(today())=9,10,
IF(MONTH(today())=10,1,
IF(MONTH(today())=11,1,
IF(MONTH(today())=12,1)))))))))))),1),
IF(Billing_Schedule_Code__c=2,
DATE(YEAR(today())+(IF(MONTH(today())>10,1,0)),
IF(MONTH(today())=1,2,
IF(MONTH(today())=2,5,
IF(MONTH(today())=3,5,
IF(MONTH(today())=4,5,
IF(MONTH(today())=5,8,
IF(MONTH(today())=6,8,
IF(MONTH(today())=7,8,
IF(MONTH(today())=8,11,
IF(MONTH(today())=9,11,
IF(MONTH(today())=10,11,
IF(MONTH(today())=11,2,
IF(MONTH(today())=12,2)))))))))))),1),
IF(Billing_Schedule_Code__c=3,
DATE(YEAR(today())+(IF(MONTH(today())>11,1,0)),
IF(MONTH(today())=1,3,
IF(MONTH(today())=2,3,
IF(MONTH(today())=3,6,
IF(MONTH(today())=4,6,
IF(MONTH(today())=5,6,
IF(MONTH(today())=6,9,
IF(MONTH(today())=7,9,
IF(MONTH(today())=8,9,
IF(MONTH(today())=9,12,
IF(MONTH(today())=10,12,
IF(MONTH(today())=11,12,
IF(MONTH(today())=12,3)))))))))))),1),today())))
2 réponses
You will have to double check that each IF() has a value to test, a true, and a false. The easiest way to do this with such a long formula is to use separate lines and indentation to make it easier to read.
You might also simplify the formula and build each piece of the DATE() value separately to make formula reading easier:
In the below, we are building a DATE that has a year, month, and day parameter. The CASE is used instead of nested IFs, and is only used to find the correct 'month' value.
DATE(YEAR(today()) + IF(MONTH(today()) > (9 - 1 + Billing_Schedule_Code__c ), 1, 0),
CASE(Billing_Schedule_Code__c,
1,
IF (MONTH(today()) < 4, 4,
IF (MONTH(today()) < 7, 7, IF(MONTH(today()) < 10, 10, 1)
)
),
2,
IF (MONTH(today()) > 1 && MONTH(today()) < 5, 5,
IF (MONTH(today()) > 1 && MONTH(today()) < 8, 8,
IF (MONTH(today()) > 1 && (MONTH(today()) < 11, 11, 2)
)
),
IF (MONTH(today()) > 2 && MONTH(today()) < 6, 6,
IF (MONTH(today()) > 2 && MONTH(today()) < 9, 9,
IF (MONTH(today()) > 2 && (MONTH(today()) < 12, 12, 3)
)
)
),
1)