Jun 08 2022 05:31 PM
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.
Jun 09 2022 12:12 AM
SolutionHi @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"
)
Jun 09 2022 12:55 AM - edited Jun 09 2022 12:57 AM
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?
Jun 09 2022 01:53 AM
Jun 09 2022 02:19 AM
Jun 09 2022 03:23 AM
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
)
Jun 15 2022 06:01 PM
Jun 09 2022 12:12 AM
SolutionHi @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"
)