Formula to incorporate only not blank cells

[lynnzrae]

I want to create a column that returns if a project is expired.  I only want to apply this formula to rows that do not have a blank value under Permit Extension Date. 

If the Permit Extension Date is blank it should either return a blank or "not applicable" value. 

For those that have a permit extension date (is not blank), the value should return as expired if the permit extension date has passed (is less than today) and the doucmentation fo complete date is blank OR the documentation of completion date is after the permit extension date.  See below:

Permit Extension Date	Documentation of Completion Date	Expired, Not Expired, Not Applicable
	6/23/2024	Not Applicable
6/6/2025		Not Expired
5/3/2024		Expired
7/15/2024	6/1/2024	Not Expired

This is the formula that I came up with so far but it is returning as expired when the permit extension date is blank

Permit Extension Expiration & Construction Incomplete =

IF(

    NOT(ISBLANK(TPermitExpiration[Permit Extension Date]))

    &&

    VALUE(TPermitExpiration[Permit Extension Date])< TPermitExpiration[Construction Documentation of Completion Date]

        || (

            ISBLANK ( TPermitExpiration[Construction Documentation of Completion Date] )

                && TPermitExpiration[Permit Extension Date] < TODAY()

           ),

            "Expired",

            "Not Expired"

)

[Anonymous · Accepted solution]

Hi,@lynnzrae 

Your idea is very good. It's very close to the right result.

Regarding the issue you raised, my solution is as follows:

1.First I have created the following table and the column names and data are the data you have given:

2.Create calculated column references:

status =
IF (
    ISBLANK ( 'TPermitExpiration'[Permit Extension Date] ),
    "Not Applicable",
    IF (
        'TPermitExpiration'[Permit Extension Date] > TODAY ()
            || (
                NOT ( ISBLANK ( 'TPermitExpiration'[Documentation of Completion Date] ) )
                    && 'TPermitExpiration'[Documentation of Completion Date] < TODAY ()
            ),
        "Not Expired",
        "Expired"
    )
)

3.Here's my final result, which I hope meets your requirements.

If you have any new questions, please contact us.

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lynnzrae]

This worked perfectly, thank you.