Forum Discussion
Tasks
Does this work?
=IFS(ISNUMBER(MATCH("off", B2:G2, 0)), "Off", COUNTIF(B2:G2, "<>")=0, IF(A2<TODAY(), "Delay in Task Monitoring", "Not Completed"), COUNTIF(B2:G2, "=")>0, IF(A2<TODAY(), "Delay in Task Monitoring", IF(A2=TODAY(), "In Progress", "Not Yet Due")), TRUE, "Completed")
Does this work?
=IFS(ISNUMBER(MATCH("off", B2:G2, 0)), "Off", COUNTIF(B2:G2, "<>")=0, IF(A2<TODAY(), "Delay in Task Monitoring", "Not Completed"), COUNTIF(B2:G2, "=")>0, IF(A2<TODAY(), "Delay in Task Monitoring", IF(A2=TODAY(), "In Progress", "Not Yet Due")), TRUE, "Completed")
- Wow, it finally works, I can't describe my happy now, I really thank you, I tired you with me a lot
I'm still not sure what you want. Perhaps
=IF(ISNUMBER(MATCH("off",B2:G2,0)),"Off",IF(COUNTIF(B2:G2,"=")=0,"Completed",IF(A2<TODAY(),"Delay in Task Monitoring",IF(A2=TODAY(),IF(COUNTIF(B2:D2,"<>")+COUNTIF(F2:G2,"<>")=0,"Not Completed","In Progress"),IF(COUNTIF(B2:D2,"<>")+COUNTIF(F2:G2,"<>")=0,"Not Yet Due","In Progress")))))
or else
=IF(ISNUMBER(MATCH("off",B2:G2,0)),"Off",IF(COUNTIF(B2:G2,"=")=0,"Completed",IF(A2<TODAY(),"Delay in Task Monitoring",IF(A2=TODAY(),IF(COUNTIF(B2:D2,"<>")+COUNTIF(F2:G2,"<>")=0,"Not Completed","In Progress"),"Not Yet Due"))))
- I never blame you. I truly thank you for so much time you have given me. And I know the problem is the difference between "not completed" and "in progress", my main idea is that if the person concerned starts the day and he still hasn't started filling in the tasks, the phrase "not completed" appears, and if it starts to fill in the tasks, it shows “in progress.” This is exactly the difference.
I give up.
- It works but what about "not completed" 🙂
=IFS(ISNUMBER(MATCH("off", B2:G2, 0)), "Off", ((B2<>"")+(C2<>"")+(D2<>"")+(F2<>"")+(G2<>""))=0, IF(A2<TODAY(), "Delay in Task Monitoring", "Not Yet Due"), ((B2="")+(C2="")+(D2="")+(F2="")+(G2=""))>0, IF(A2<TODAY(), "Delay in Task Monitoring", IF(A2=TODAY(), "In Progress", "Not Yet Due")), TRUE, "Completed")
- It's work and a great idea, but like you told me earlier, it's going to be complicated because the problem arose is that the later dates show "not completed" and not show "not yet due".
How about
=IFS(ISNUMBER(MATCH("off", B2:G2, 0)), "Off", ((B2<>"")+(C2<>"")+(D2<>"")+(F2<>"")+(G2<>""))=0, IF(A2<TODAY(), "Delay in Task Monitoring", "Not Completed"), ((B2="")+(C2="")+(D2="")+(F2="")+(G2=""))>0, IF(A2<TODAY(), "Delay in Task Monitoring", IF(A2=TODAY(), "In Progress", "Not Yet Due")), TRUE, "Completed")
HansVogelaar I know that and it's okay only because I want it to be more accurate, and because if tomorrow comes and the person concerned does not complete the tasks, it will appear that he is late and that is enough, thank you again
That's needlessly complicated.
- Because I need the total column in the middle of the table, I cannot move it, but the function in the case of tasks works correctly in tasks that are at a later date "The task has not come yet" even though there is a sum equation so I think it can be solved through an equation in the total column so that it is There is a condition in the total column with formula like = sumif/isblank, the condition is that the cell with sum formula is empty unless numbers are filled, I mean that normally, if there is a summation equation and there are no numbers to sum, the equation will appear 0. I want the cell sum it to be "blank" unless there is numbers are filled in that show the result of the sum formula
In that case, you should not include cells with a sum (or other) formula in the range used to determine the status. You can do this by moving column E to the right, to column G. The formulas now refer to B2:F2 etc., excluding the formula.
HansVogelaar Try this in Excel your attached before, In the case of the task that is "not completed", add a sum formula in the Total column, the sum will appear as 0 because you have not entered the required numbers yet, the formula will understand that there is a fill in one of the cell, so because it is today's date, the status task will be "in progress".If we can solve it, it will be better, if we can't, that's okay, because the next day, if he doesn't complete the task, it will appear "delay in task monitoring"
I thought the formula already did that...
yes, There is a simple thing, but it is not important, and which is if today's date and there are an in one of the cells, sum formula, it will be "in progress" not "not completed".
I really thank you for what you did for me, I appreciate your efforts