0

I am trying to get an output of Zodiac sign using date of birth field. Here is my code using IF ()

please help me frequently getting syntax error of expected 3, received 1. Wh could me the mistake?

IF(
    (
        (
            NOT(ISBLANK( date_of_Birth__c  )),
                (   
                    IF((Month(date_of_Birth__c) = 12) && (day(date_of_Birth__c) <22 )), ' ♐sagittarius',  "NULL",
                    IF((Month(date_of_Birth__c)= 12) && (day(date_of_Birth__c) >=22 )), ' ♑ Capricorn',  "NULL",
                    IF((Month(date_of_Birth__c)= 11) && (day(date_of_Birth__c) <23 )), 'Scorpio',  "NULL",
                    IF((Month(date_of_Birth__c)= 11) && (day(date_of_Birth__c) >=23 )), 'sagittarius',  "NULL",
                    IF((Month(date_of_Birth__c)= 10) && (day(date_of_Birth__c) <23 )), 'Libra',  "NULL",
                    IF((Month(date_of_Birth__c)= 10) && (day(date_of_Birth__c) >=23 )), 'Scorpio',  "NULL",
                    IF((Month(date_of_Birth__c)= 9 )&& (day(date_of_Birth__c) <23 )), 'Virgo',  "NULL",
                    IF((Month(date_of_Birth__c)= 9 )&& (day(date_of_Birth__c) >=23 )), 'Libra',  "NULL",
                    IF((Month(date_of_Birth__c)= 8 )&& (day(date_of_Birth__c) <23 )), 'Leo',  "NULL",
                    IF((Month(date_of_Birth__c)= 8 )&& (day(date_of_Birth__c) >=23 )), 'Virgo',  "NULL",
                    IF((Month(date_of_Birth__c)= 7 )&& (day(date_of_Birth__c) <23 )), 'Cancer',  "NULL",
                    IF((Month(date_of_Birth__c)= 7 )&& (day(date_of_Birth__c) >=23 )), 'Leo',  "NULL",
                    IF((Month(date_of_Birth__c)= 6 )&& (day(date_of_Birth__c) <22 )), 'Gemini',  "NULL",
                    IF((Month(date_of_Birth__c)= 6 )&& (day(date_of_Birth__c) >=22 )), 'Cancer',  "NULL",
                    IF((Month(date_of_Birth__c)= 5 )&& (day(date_of_Birth__c) <21 )), 'Taurus',  "NULL",
                    IF((Month(date_of_Birth__c)= 5 )&& (day(date_of_Birth__c) >=21 )), 'Gemini',  "NULL",
                    IF((Month(date_of_Birth__c)= 4 )&& (day(date_of_Birth__c) <21 )), 'Aries',  "NULL",
                    IF((Month(date_of_Birth__c)= 4 )&& (day(date_of_Birth__c) >=21 )), 'Taurus',  "NULL",
                    IF((Month(date_of_Birth__c)= 3 )&& (day(date_of_Birth__c) <21 )), 'Pisces',  "NULL",
                    IF((Month(date_of_Birth__c)= 3 )&& (day(date_of_Birth__c) >=21 )), 'Aries',  "NULL",
                    IF((Month(date_of_Birth__c)= 2 )&& (day(date_of_Birth__c) <19 )), 'Aquarius',  "NULL",
                    IF((Month(date_of_Birth__c)= 2 )&& (day(date_of_Birth__c) >=19 )), 'Pisces',  "NULL" ,
                    IF((Month(date_of_Birth__c)= 1 )&& (day(date_of_Birth__c) <21 )), 'Capricorn',  "NULL",
                    IF((Month(date_of_Birth__c)= 1 )&& (day(date_of_Birth__c) >=21 )), 'Aquarius',  "NULL",
                )
            ),"NULL"
        )
    )

1 Answer 1

2

The error is very straightforward.

The IF() function in a formula is not the same as an if in Apex, and it requires 3 arguments:

  1. The condition to check
  2. The value to return if the condition is true
  3. The value to return if the condition is false

All 3 are required, and you're only providing one. Hence the error telling you "expected 3, received 1"

So instead of

/* You're currently ending the IF() after only giving it the condition */
IF(
   (Month(date_of_Birth__c) = 12) && (day(date_of_Birth__c) <22 )
), ' ♐sagittarius',  "NULL"

You'd need

IF(
    /* condition */
    (Month(date_of_Birth__c) = 12) && (day(date_of_Birth__c) <22 ), 
    /* then */
    ' ♐sagittarius',
    /* else */
    "NULL"
)

Though you would need to replace the "null" with the next IF() to chain this into a long if-else if-else.

You'll probably want to look at using the CASE() function to do the initial check against the month number. It should end up being less to type (and thus less prone to mistakes). The Formula Operators and Functions documentation is your friend.


The following formula would work:

IF(
    /* Condition */
    ISBLANK( date_of_Birth__c),
    /* Then */
    NULL,
    /* Else */
    /* The first argument to CASE() is the variable you want to test */
    /* Each pair of arguments after that is the value you want to */
    /*   match, followed by the value to return */
    CASE(MONTH(date_of_Birth__c),
        1, IF(DAY(date_of_Birth__c) < 21,'Capricorn','Aquarius'),
        2, IF(DAY(date_of_Birth__c) < 19,'Aquarius','Pisces'),
        3, IF(DAY(date_of_Birth__c) < 21,'Pisces','Aries'),
        4, IF(DAY(date_of_Birth__c) < 21,'Aries','Taurus'),
        5, IF(DAY(date_of_Birth__c) < 21,'Taurus','Gemini'),
        6, IF(DAY(date_of_Birth__c) < 22,'Gemini','Cancer'),
        7, IF(DAY(date_of_Birth__c) < 23,'Cancer','Leo'),
        8, IF(DAY(date_of_Birth__c) < 23,'Leo','Virgo'),
        9, IF(DAY(date_of_Birth__c) < 23,'Virgo','Libra'),
        10, IF(DAY(date_of_Birth__c ) < 23,'Libra','Scorpio'),
        11, IF(DAY(date_of_Birth__c ) < 23,'Scorpio','Sagittarius'),
        /* The final argument is the value to return if none */
        /*   of the previous values were matched (and this is required) */
        IF(day(date_of_Birth__c) < 22, 'Sagittarius', 'Capricorn')
    )
)
2
  • I had already written a formula out all the way, but I would have otherwise duplicated your answer. I hope you don't mind I edited it in. 😅
    – sfdcfox
    May 16 at 15:08
  • No worries. You did a great job by helping me and showing me new method. :-) May 16 at 15:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.