if statement with between conditions

[Anonymous]

Hi, Good Day.

Need help on my formula below. i am trying to create an if statement with between conditions but is getting error message.

DurationFilter =

If(INCIDENT_FACT[DURATION_HOURS] <=0.5, "Less Than 30 Mins"),

    If(INCIDENT_FACT[DURATION_HOURS] >0.5 And <=0.75, "Past 30 Mins"),

    If(INCIDENT_FACT[DURATION_HOURS] >0.75 And <1, "Past 45 Mins"),

    If(INCIDENT_FACT[DURATION_HOURS] >=1 And <1.5, "Past 1 Hr"),

    If(INCIDENT_FACT[DURATION_HOURS] >=1.5 And <=2, "Past 1Hr 30Mins"),

    If(INCIDENT_FACT[DURATION_HOURS] >2 And <2.5, "Past 2 Hr"),

    If(INCIDENT_FACT[DURATION_HOURS] >=2.5 And <=3, "Past 2Hr 30Mins"),

    If(INCIDENT_FACT[DURATION_HOURS] >3 And <3.5, "Past 3 Hr"),

    If(INCIDENT_FACT[DURATION_HOURS] >=3.5 And <=4, "Past 3Hr 30Mins"),

    If(INCIDENT_FACT[DURATION_HOURS] >4, "Past 3 Hr"),

If(BLANK())

Appreciate your help on this.

this is the error i am getting..

[Anonymous]

Thank you. 

[v-luwang-msft · Accepted solution]

Hi @Anonymous ,

Test the below:

DurationFilter =
IF (
    INCIDENT_FACT[DURATION_HOURS] <= 0.5,
    "Less Than 30 Mins",
    IF (
        INCIDENT_FACT[DURATION_HOURS] > 0.5
            && INCIDENT_FACT[DURATION_HOURS] <= 0.75,
        "Past 30 Mins",
        IF (
            INCIDENT_FACT[DURATION_HOURS] > 0.75
                && INCIDENT_FACT[DURATION_HOURS] < 1,
            "Past 45 Mins",
            IF (
                INCIDENT_FACT[DURATION_HOURS] >= 1
                    && INCIDENT_FACT[DURATION_HOURS] < 1.5,
                "Past 1 Hr",
                IF (
                    INCIDENT_FACT[DURATION_HOURS] >= 1.5
                        && INCIDENT_FACT[DURATION_HOURS] <= 2,
                    "Past 1Hr 30Mins",
                    IF (
                        INCIDENT_FACT[DURATION_HOURS] > 2
                            && INCIDENT_FACT[DURATION_HOURS] < 2.5,
                        "Past 2 Hr",
                        IF (
                            INCIDENT_FACT[DURATION_HOURS] >= 2.5
                                && INCIDENT_FACT[DURATION_HOURS] <= 3,
                            "Past 2Hr 30Mins",
                            IF (
                                INCIDENT_FACT[DURATION_HOURS] > 3
                                    && INCIDENT_FACT[DURATION_HOURS] < 3.5,
                                "Past 3 Hr",
                                IF (
                                    INCIDENT_FACT[DURATION_HOURS] >= 3.5
                                        && INCIDENT_FACT[DURATION_HOURS] <= 4,
                                    "Past 3Hr 30Mins",
                                    IF ( INCIDENT_FACT[DURATION_HOURS] > 4, "Past 3 Hr", BLANK () )
                                )
                            )
                        )
                    )
                )
            )
        )
    )
)

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien

[HotChilli]

OK, I can see what the problem is, however, the way to debug these things is to simplify the IF statement to something very basic that works: So, copy the code into notepad and remove almost everything except one IF test and the simplest of options.

Let me know how you get on.