Forum Discussion

Row89's avatar
Row89
Copper Contributor
Jun 09, 2022
Solved

Help with IF ISBLANK AND formula

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.

excel
Formulas and Functions
  • Lorenzo's avatar
    Lorenzo
    Jun 09, 2022

    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"
)

7 Replies

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Jun 09, 2022

    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"
)
    • Row89's avatar
      Row89
      Copper Contributor
      Jun 09, 2022

      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?

      • Lorenzo's avatar
        Lorenzo
        Silver Contributor
        Jun 09, 2022
        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

Resources