Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

IF AND for Status Change

Topic Labels: Formulas
Solved
Jump to Solution
1910 5
cancel
Showing results for 
Search instead for 
Did you mean: 
lucas__mar
5 - Automation Enthusiast
5 - Automation Enthusiast

Hello, I'm trying to create a Formula that will change Status according to the fields "Start Date" & "End Date". 

If Today > End Date = "Delayed"

If Today < End Date = "In Progress"

If Today < Start Date = "Planning"

I have written the following formula that works for "Delayed", but shows "True" instead of "In Progress" or "Planning"

IF(TODAY() > {End Date},"Delayed", AND(IF(TODAY() < {End Date},"In Progress"),AND(IF(TODAY() < {Start Date},"Planning"))))
 
Anyway can identify my mistake? Thank you!!!
2 Solutions

Accepted Solutions
Ben_Young1
11 - Venus
11 - Venus

Hey @lucas__mar

Give this a shot:

IF(
    {End Date},
    IF(
        TODAY() > {End Date},
        "Delayed",
        IF(
            TODAY() < {End Date},
            "In Progress",
            IF(
                TODAY() < {Start Date},
                "Planning"
            )
        )
    ),
    IF(
        {Start Date},
        IF(
            TODAY() < {Start Date},
            "Planning"
        )
    )
)

See Solution in Thread

Hey @lucas__mar

Glad to see that the original formula worked as you'd hoped.
For the edit you're describing, give this a shot and let me know if it works or if something goes funky with it.
I haven't been able to test it yet, so I might have missed something:

IF(
    {Completion},
    SWITCH(
        {Completion},
        "Yes",
            "Done",
        "No",
            IF(
                {End Date},
                IF(
                    TODAY() > {End Date},
                    "Delayed",
                    IF(
                        TODAY() < {End Date},
                        "In Progress",
                        IF(
                            TODAY() < {Start Date},
                            "Planning",
                            ""
                        )
                    )
                ),
                IF(
                    {Start Date},
                    IF(
                        TODAY() < {Start Date},
                        "Planning",
                        ""
                    )
                )
            )
    ),
    IF(
        {End Date},
        IF(
            TODAY() > {End Date},
            "Delayed",
            IF(
                TODAY() < {End Date},
                "In Progress",
                IF(
                    TODAY() < {Start Date},
                    "Planning"
                )
            )
        ),
        IF(
            {Start Date},
            IF(
                TODAY() < {Start Date},
                "Planning"
            )
        )
    )
)

 

See Solution in Thread

5 Replies 5
Ben_Young1
11 - Venus
11 - Venus

Hey @lucas__mar

Give this a shot:

IF(
    {End Date},
    IF(
        TODAY() > {End Date},
        "Delayed",
        IF(
            TODAY() < {End Date},
            "In Progress",
            IF(
                TODAY() < {Start Date},
                "Planning"
            )
        )
    ),
    IF(
        {Start Date},
        IF(
            TODAY() < {Start Date},
            "Planning"
        )
    )
)
lucas__mar
5 - Automation Enthusiast
5 - Automation Enthusiast

This worked like a charm @Ben_Young1 
Thank you VERY VERY much 😃

lucas__mar
5 - Automation Enthusiast
5 - Automation Enthusiast

Hey @Ben_Young1 I'm trying to add a "Done" to that field, based on a value in an added single select field named "Completion" (Yes, No). The idea is that when the field is changed to "Yes", then the Status field changes to "Done". I tried with the IFs of your formula, but can't seem to get it right. Thxs for the help!

Hey @lucas__mar

Glad to see that the original formula worked as you'd hoped.
For the edit you're describing, give this a shot and let me know if it works or if something goes funky with it.
I haven't been able to test it yet, so I might have missed something:

IF(
    {Completion},
    SWITCH(
        {Completion},
        "Yes",
            "Done",
        "No",
            IF(
                {End Date},
                IF(
                    TODAY() > {End Date},
                    "Delayed",
                    IF(
                        TODAY() < {End Date},
                        "In Progress",
                        IF(
                            TODAY() < {Start Date},
                            "Planning",
                            ""
                        )
                    )
                ),
                IF(
                    {Start Date},
                    IF(
                        TODAY() < {Start Date},
                        "Planning",
                        ""
                    )
                )
            )
    ),
    IF(
        {End Date},
        IF(
            TODAY() > {End Date},
            "Delayed",
            IF(
                TODAY() < {End Date},
                "In Progress",
                IF(
                    TODAY() < {Start Date},
                    "Planning"
                )
            )
        ),
        IF(
            {Start Date},
            IF(
                TODAY() < {Start Date},
                "Planning"
            )
        )
    )
)

 

lucas__mar
5 - Automation Enthusiast
5 - Automation Enthusiast

Thank you again @Ben_Young1 this is perfect! I think after spending a few minutes I now also understand the logic behind. It's a good time to import that Formulas Base that's around and study them in depth hehe

Thank you!!!!