Help with IF ISBLANK AND formula

[Row89]

Hi

I'm trying to do a formula but I got stuck at the below:

=IF(ISBLANK([@[Date Completed]]),"",IF(AND([@[Date Completed]]<=[@[Due Date]]), "Completed on Time", "Completed Late"))

I want it to be able to do the below:

if the date completed is blank AND the status column is "completed" then show "Missing completed date" but if the completed date column contains a date and is equal to or less than the "due date" AND the status column is "completed" then show "Completed on time" otherwise if the completed date is more than the due date and the status column is "completed" then show "Completed late"

I'm having trouble only picking up "completed" items in the status column as it contains other statuses in there too like "not started" etc.

[#2 · Accepted solution]

Hi @Row89 

(dates in dd/mm/yy format)

in D2:

 =IFS(
    AND(ISNUMBER([@[Date Completed]]),ISBLANK([@Status])), "Missing status",
    [@Status] <> "completed", "",
    ISBLANK([@[Date Completed]]), "Missing completed date",
    AND(ISNUMBER([@[Date Completed]]),[@[Date Completed]]<=[@[Due Date]]), "Completed on time",
    TRUE, "Completed late"
)

[#3]

you're a genius, thank you very much!!!

Would this also work in MS Lists as a conditional column or Power BI custom column or would I have to change the format of the formula?

[#4]

You're welcome & Thanks for providing feedback
Re. MS Lists no idea as I don't know the product
Re. Power BI/Power Query the same thing can be achieved but the formula will be different as the products don't "share" any function with Excel. If you want an example please open a new discussion - Thanks

[#5]

Thank you, I've posted it here

https://community.powerbi.com/t5/Desktop/Help-with-converting-excel-formula-to-Power-BI/m-p/2568671#...

[#6]

@Row89 

I don't have a PowerBI account so can't reply on the PowerBI community. Could be something like:

= Table.AddColumn(PreviousStepName, "Comment", each
    if ([Date Completed] is date and [Status] is null) then "Missing status"
    else if [Status] <> "Completed" then null
    else if [Date Completed] is null then "Missing completed date"
    else if ([Date Completed] is date and [Date Completed] <= [Due Date]) then "Completed on time"
    else "Completed late",
    type text
)

[#7]

Thank you for your help on this, much appreciated!

[#8]

You're welcome. Good day...