Adjusting my formula

[RonaldvdH]

Ive got a calculated column based on multiple variables

Test = IF(Adressen[Status Site-Survey]<>"";(

IF(Adressen[Status Site-Survey]<>"null";(

IF(AND(Adressen[site-survey gesloten]=BLANK();Adressen[Sisu gepland]=BLANK());"Sisu nog niet gepland";(

IF(AND(Adressen[site-survey gesloten]=BLANK();Adressen[Sisu gepland]<TODAY());"Sisu te laat";(

IF(AND(Adressen[site-survey gesloten]=BLANK();Adressen[Sisu gepland]>TODAY()-5);"Sisu inleveren binnen 5 dag(en)";(

IF(AND(Adressen[site-survey gesloten]=BLANK();Adressen[Sisu gepland]>TODAY()-4);"Sisu inleveren binnen 4 dag(en)";(

IF(AND(Adressen[site-survey gesloten]=BLANK();Adressen[Sisu gepland]>TODAY()-3);"Sisu inleveren binnen 3 dag(en)";(

IF(AND(Adressen[site-survey gesloten]=BLANK();Adressen[Sisu gepland]>TODAY()-2);"Sisu inleveren binnen 2 dag(en)";(

IF(AND(Adressen[site-survey gesloten]=BLANK();Adressen[Sisu gepland]>TODAY()-1);"Sisu inleveren binnen 1 dag(en)";(

IF(Adressen[site-survey gesloten]<>BLANK();"Sisu gereed";"geen idee")))))))))))))))))))

However it looks like the variable ....>TODAY()-5 -4 -3 etc doesn't work because the only values given is "Sisu inleveren binnen 5 dag(en)"

But im guessing you guys understand what im trying to do but im sure you know a smarter way 

[v-lid-msft]

Hi @RonaldvdH ,

We can adjust your formula to following:

Test =
IF (
    AND (
        Adressen[Status Site-Survey] <> "";
        Adressen[Status Site-Survey] <> "null"
    );
    IF (
        AND (
            Adressen[site-survey gesloten] = BLANK ();
            Adressen[Sisu gepland] = BLANK ()
        );
        "Sisu nog niet gepland";
        IF (
            Adressen[site-survey gesloten] <> BLANK ();
            "Sisu gereed";
            IF (
                Adressen[Sisu gepland] > TODAY ();
                "geen idee";
                IF (
                    Adressen[Sisu gepland]
                        > TODAY () - 1;
                    "Sisu inleveren binnen 1 dag(en)";
                    IF (
                        Adressen[Sisu gepland]
                            > TODAY () - 2;
                        "Sisu inleveren binnen 2 dag(en)";
                        IF (
                            Adressen[Sisu gepland]
                                > TODAY () - 3;
                            "Sisu inleveren binnen 3 dag(en)";
                            IF (
                                Adressen[Sisu gepland]
                                    > TODAY () - 4;
                                "Sisu inleveren binnen 4 dag(en)";
                                IF (
                                    Adressen[Sisu gepland]
                                        > TODAY () - 5;
                                    "Sisu inleveren binnen 5 dag(en)";
                                    "Sisu te laat"
                                )
                            )
                        )
                    )
                )
            )
        )
    )
)

Or the SWITCH Function Version:

Test =
IF (
    AND (
        Adressen[Status Site-Survey] <> "";
        Adressen[Status Site-Survey] <> "null"
    );
    IF (
        AND (
            Adressen[site-survey gesloten] = BLANK ();
            Adressen[Sisu gepland] = BLANK ()
        );
        "Sisu nog niet gepland";
        IF (
            Adressen[site-survey gesloten] <> BLANK ();
            "Sisu gereed";
            VAR temp =
                Adressen[Sisu gepland] - TODAY ()
            RETURN
                SWITCH (
                    TRUE ();
                    temp > 0; "green idee";
                    temp > -1; "Sisu inleveren binnen 1 dag(en)";
                    temp > -2; "Sisu inleveren binnen 2 dag(en)";
                    temp > -3; "Sisu inleveren binnen 3 dag(en)";
                    temp > -4; "Sisu inleveren binnen 4 dag(en)";
                    temp > -5; "Sisu inleveren binnen 5 dag(en)";
                    "Sisu te laat"
                )
        )
    )
)

If it doesn't meet your requirement, kindly share your sample data and expected result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jdbuchanan71]

Hello @RonaldvdH 

I think it is because you are checking for Adressen[Sisu gepland]>TODAY()-5 first which is catching everything.  We can also simplify the measure some like so:

Test =
IF (
    Adressen[Status Site-Survey] <> ""
        && Adressen[Status Site-Survey] <> "null"
        && Adressen[site-survey gesloten] = BLANK ();
    SWITCH (
        TRUE ();
        Adressen[Sisu gepland] = BLANK (); "Sisu nog niet gepland";
        Adressen[Sisu gepland] < TODAY (); "Sisu te laat";
        Adressen[Sisu gepland] > TODAY () - 1; "Sisu inleveren binnen 1 dag(en)";
        Adressen[Sisu gepland] > TODAY () - 2; "Sisu inleveren binnen 2 dag(en)";
        Adressen[Sisu gepland] > TODAY () - 3; "Sisu inleveren binnen 3 dag(en)";
        Adressen[Sisu gepland] > TODAY () - 4; "Sisu inleveren binnen 4 dag(en)";
        Adressen[Sisu gepland] > TODAY () - 5; "Sisu inleveren binnen 5 dag(en)"
    )
)

[RonaldvdH]

@jdbuchanan71 your formula doesn't work and also returns just one value "Sisu inleveren binnen 1 dag(en)"

all other values (2 dagen, 3 dagen etc) don't show up in the results 

[v-lid-msft]

Hi @RonaldvdH ,

How about the result after you follow the suggestions mentioned in my original post?

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jdbuchanan71]

@RonaldvdH 

The problem is, everything before today gets caught by

Adressen[Sisu gepland] < TODAY (), "Sisu te laat",

Then today or later is always > TODAY() - 1 OR TODAY() - 5 so whatever one you put first catches all the rest.

What is it that you are trying to show?

[jdbuchanan71]

Is it maybe something like this you are looking for?

Test2 = 
IF (
    Adressen[Status Site-Survey] <> ""
        && Adressen[Status Site-Survey] <> "null"
        && Adressen[site-survey gesloten] = BLANK (),
    SWITCH (
        TRUE (),
        Adressen[Sisu gepland] = BLANK (), "Sisu nog niet gepland",
        Adressen[Sisu gepland] < TODAY (), "Sisu te laat",
        Adressen[Sisu gepland] > TODAY () + 4, "Sisu inleveren binnen 5 dag(en)",
        Adressen[Sisu gepland] > TODAY () + 3, "Sisu inleveren binnen 4 dag(en)",
        Adressen[Sisu gepland] > TODAY () + 2, "Sisu inleveren binnen 3 dag(en)",
        Adressen[Sisu gepland] > TODAY () + 1, "Sisu inleveren binnen 2 dag(en)",
        Adressen[Sisu gepland] > TODAY () , "Sisu inleveren binnen 1 dag(en)",
        Adressen[Sisu gepland] = TODAY () , "Sisu heute"
    )
)

[RonaldvdH]

@jdbuchanan71 this formula is getting close except there is no formula if the result is bigger then today ()+5

Besides ive made a fault in my reasoning because the formula shouldn't be Today () - 5 but Today () + 5

For example if i need tot turn in my report on november 5th that means the formula should read 'Sisu inleveren binnen 5 dag(en)' given it's november 1st.

Basically that means i have 5 days to turn in my report but if i have more then 5 days left it should read that i have more then 5 days left so the formula should state that IF the result is bigger then Today () + 5 then it should read 'Sisu inleveren > 5 dag(en)

[jdbuchanan71]

@RonaldvdH 

That would be this then.

Test2 = 
IF (
    Adressen[Status Site-Survey] <> ""
        && Adressen[Status Site-Survey] <> "null"
        && Adressen[site-survey gesloten] = BLANK (),
    SWITCH (
        TRUE (),
        Adressen[Sisu gepland] = BLANK (), "Sisu nog niet gepland",
        Adressen[Sisu gepland] < TODAY (), "Sisu te laat",
        Adressen[Sisu gepland] > TODAY () + 5, "Sisu inleveren > 5 dag(en)",
        Adressen[Sisu gepland] > TODAY () + 4, "Sisu inleveren binnen 5 dag(en)",
        Adressen[Sisu gepland] > TODAY () + 3, "Sisu inleveren binnen 4 dag(en)",
        Adressen[Sisu gepland] > TODAY () + 2, "Sisu inleveren binnen 3 dag(en)",
        Adressen[Sisu gepland] > TODAY () + 1, "Sisu inleveren binnen 2 dag(en)",
        Adressen[Sisu gepland] > TODAY () , "Sisu inleveren binnen 1 dag(en)",
        Adressen[Sisu gepland] = TODAY () , "Sisu heute"
    )
)