Formula column to show as blank if a date is missing

Platform discussions
1

I am using this formula to show status of an item of it is past it’s target date, on target or completed before it’s target date. I also want the column to be “blank” if one of the dates was missing (Legals Completed). See formula below:

IF({Legals Completed}<{Timeline#End},“Within Target”, IF({Legals Completed}>{Timeline#End},“Past Target Date”, IF({Legals Completed}={Timeline#End},“On Target”, “”)))

Are there any experts out there?

Thanks

2

Firstly, when you start using more complex formulas, it’s definitely worth using indentation to visualize what’s happening.

It’s not always necessary but generally good practice to use “FORMAT_DATE” when working with dates, just so that you can guarantee consistent results.

For showing as blank when a date is missing, that’s just a case of nesting another IF statement.

IF(
    OR(
        {Legals Completed}="",
        {Timeline#End}=""
    ),
    "",
    IF(
        FORMAT_DATE({Legals Completed},"YYYY-MM-DD")<FORMAT_DATE({Timeline#End},"YYYY-MM-DD"),
        "Within Target",
        IF(
            FORMAT_DATE({Legals Completed},"YYYY-MM-DD")>FORMAT_DATE({Timeline#End},"YYYY-MM-DD"),
            "Past Target Date",
            IF(
                FORMAT_DATE({Legals Completed},"YYYY-MM-DD")=FORMAT_DATE({Timeline#End},"YYYY-MM-DD"),
                "On Target",
                ""
            )
        )
    )
)

If you’re considering adding further complexity to this formula, you might want to use CONCATENATE, as this command will prevent you from nesting statements too deep. You can easily combine mutually exclusive statements such as these in that way, returning a blank value when the statements are false.

Feel free to reach out if you need help. :+1:

1 Like
3

Thank you so much for this.

1 Like