Forum Discussion

GeorgieAnne's avatar
GeorgieAnne
Iron Contributor
Jul 06, 2026

Can anyone help me with this SWITCH function

Hello Everyone,

This SWITCH function is used to produce a number that then I can use to sort workers and their functions.

=SWITCH(TRUE,

    IF(AND(ISNUMBER(J2)=TRUE,M2="Working")=TRUE,5,0),

    IF(AND(ISNUMBER(K2)=TRUE,M2="Working")=TRUE,500,0),

    IF(AND(ISNUMBER(J2)=TRUE,M2="Special Project")=TRUE,15,0),

    IF(AND(ISNUMBER(K2)=TRUE,M2="Special Project")=TRUE,515,0),

    IF(AND(ISNUMBER(J2)=TRUE,M2="Stand By")=TRUE,20,0),

    IF(AND(ISNUMBER(K2)=TRUE,M2="Stand By")=TRUE,520,0),

    IF(AND(ISNUMBER(J2)=TRUE,M2="Special Project UFN")=TRUE,25,0),

    IF(AND(ISNUMBER(K2)=TRUE,M2="Special Project UFN")=TRUE,525,0),

    50

)

In Column J I have their Start Time, from the Kronos System, and in Column K I have their End Time.

In Column M I have Tasks names that would indicate what each Team member is doing.

Each component does evaluate correctly but the function always return 50?

What am I missing here?

Thanks in Advance!

GiGi

developer
excel
Formulas and Functions
office 365

3 Replies

  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    Jul 07, 2026

    If your intent is:

    • 5/15/20/25 = task has started but not yet ended (J2 filled, K2 empty)
    • 500/515/520/525 = task has ended (K2 filled, regardless of J2)

    Then you want to check K2 first, or make the J2 condition explicitly exclude K2:

    =SWITCH(TRUE,

        AND(ISNUMBER(K2),M2="Working"),500,

        AND(ISNUMBER(J2),M2="Working"),5,

        AND(ISNUMBER(K2),M2="Special Project"),515,

        AND(ISNUMBER(J2),M2="Special Project"),15,

        AND(ISNUMBER(K2),M2="Stand By"),520,

        AND(ISNUMBER(J2),M2="Stand By"),20,

        AND(ISNUMBER(K2),M2="Special Project UFN"),525,

        AND(ISNUMBER(J2),M2="Special Project UFN"),25,

        50)

    This way, if K2 has an end time, that takes priority and returns the "500-series" number, regardless of whether J2 also has a value. Only if K2 is not a number does it fall through to check J2.

    Does that match what you're trying to track (started vs. finished), or is the distinction between J2 and K2 meant to represent something else?

  • SelinaKnow's avatar
    SelinaKnow
    Brass Contributor
    Jul 07, 2026

    The issue is that SWITCH(TRUE, ...) expects logical conditions, but your formula is passing the results of IF() instead. Replace each IF(...) with the corresponding AND(...) condition (or use IFS()), and the formula will return the expected values instead of always falling back to 50. 

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Jul 06, 2026

    Remove all the IF functions, the =TRUE parts and the ",0" at the end of each statement. Make sure that the parenthases match up tough.

    A formula that gives results (don't know if they are correct though) is this one:

    =SWITCH(TRUE,

    AND(ISNUMBER(J2),M2="Working"),5,

    AND(ISNUMBER(K2),M2="Working"),500,

    AND(ISNUMBER(J2),M2="Special Project"),15,

    AND(ISNUMBER(K2),M2="Special Project"),515,

    AND(ISNUMBER(J2),M2="Stand By"),20,

    AND(ISNUMBER(K2),M2="Stand By"),520,

    AND(ISNUMBER(J2),M2="Special Project UFN"),25,

    AND(ISNUMBER(K2),M2="Special Project UFN"),525,

    50

)