Skip to main content
I'm looking to create a field that allows our firm to know the next billing date for our customers. Custoemrs are billed on a quarterly basis, but the months vary by customer, so they're either JAJO (January, April, July, October... etc.), FMAN, or MJSD. I translated each of these terms into a billing code field, where JAJO = 1, FMAN = 2, and MJSD = 3. I'm now trying to put together a code based on today's date and the "Billing_Schedule_Code_c" to tell me the next billing date, but the following formula tells gives me the syntax error: "Error: Incorrect number of paramers for function 'IF()' Expected 3, received 2" I've tested this formula in Excel and it worked perfectly. What am I doing wrong?!

 

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 件の回答
  1. 2013年12月26日 11:15
    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)
0/9000