Sharepoint date function in conditional formula

Question

I want to add some days when the (type calculated)[No. vacante] column has "vacante 1" or "vacante 2" my code is below. The error I am getting is a syntax error or that the formula is not supported, but i can not figure out where the error is.

=IF([No. vacante]="vacante 1",(DATE(YEAR([fecha ingreso]),MONTH([fecha ingreso]),DAY([fecha ingreso])+1)),
IF([No. vacante]="vacante 2",(DATE(YEAR([fecha ingreso]),MONTH([fecha ingreso]),DAY([fecha ingreso])+2) )),
IF([No. vacante]="vacante 3",(DATE(YEAR([fecha ingreso]),MONTH([fecha ingreso]),DAY([fecha ingreso])+3))))

mpulcini mpulcini · Accepted Answer · 2012-03-28T02:22:33

There are two issues with the provided formula:

The third IF statement is nested at the wrong level (inside of IF #1 instead of IF #2).
The third IF statement also needs a default value as its third parameter, so that the formula always returns some value.

Every IF statement in Sharepoint needs three values: the expression to be evaluated, the value if true, and the value if false. Microsoft provides a more detailed explanation.

I believe the below formula should do the job. Make sure to replace default_value_goes_here with an appropriate default to be used if [No. vacante] does not equal any of vacante 1, vacante 2, or vacante 3.

=
    IF(
        [No. vacante]="vacante 1", 
        (DATE
            (YEAR([fecha ingreso]),
            MONTH([fecha ingreso]),
            DAY([fecha ingreso])+1)
        ),
        IF(
            [No. vacante]="vacante 2",
            (DATE
                (YEAR([fecha ingreso]),
                MONTH([fecha ingreso]),
                DAY([fecha ingreso])+2)
            ),
            IF(
                [No. vacante]="vacante 3",
                (DATE
                    (YEAR([fecha ingreso]),
                    MONTH([fecha ingreso]),
                    DAY([fecha ingreso])+3)
                ),
                default_value_goes_here
            )
        )
    )

Sharepoint date function in conditional formula

1 Answers