Hi,
In the attached excel file, i am trying to enter a formula to make a person Not Available for the below criteria.
The person A should not be available if:
1. The DND status (Sheet2) of person A is Do Not Disturb.
2. The status of person A is pending (Sheet 1).
3. Person A has 2 or more status as Completed.
Can someone guide me with the formula.
Thanks in advance.
where do you want the formula
Try
=IF(OR(C2="Do Not Disturb", COUNTIFS(Sheet1!$A$2:$A$6,Sheet2!$A2,Sheet1!$B$2:$B$6,"pending")>0,COUNTIFS(Sheet1!$A$2:$A$6,Sheet2!$A2,Sheet1!$B$2:$B$6,"completed")>1),"not Available","Available")
Added to sheet2
B2
Last edited by etaf; 01-01-2022 at 01:48 PM.
Wayne
if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here
If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Hi etaf,
Your formula worked perfectly. Thanks a ton for the resolution and lightning response.
Hi etaf,
Sorry i forgot to mention the 3rd condition precisely.
If Person A has 2 or more status as Completed in a day, only then the status should be Not Available.
However, the status should again be Available the next day. and so on.
Is there any way to do this?
Thanks in advance.
i dont see any dates in the sample
Hi etaf,Originally Posted by etaf
Sorry for not mentioning the date column earlier. The same is added now.
As mentioned earlier, below are the criteria.
The person should not be available if:
1. The DND status (Sheet2) of a person is Do Not Disturb.
2. Even 1 status of a person is pending (Sheet 1).
3. A person has 2 or more status as Completed on a single date.
The first two criteria are met except the third one.
As per the table in Sheet1, D has completed 2 tasks on 31-12-2021 and therefore his status will be Not Available. However, on 01-01-2022 his status should again be Available.
Could you please provide solution to the above issue.
Also, is the below point possible to implement :
In case we get extra work and all person are in pending state, I still have to make a person available so that I can assign him a second task. So if all are pending, then all persons should be Available again. Now, persons with 2 pending state should be Not Available.
Thanks in advance.
Last edited by rizwanulhasan; 01-02-2022 at 04:10 AM.
i had to make a helper column on sheet 1 to turn the date into an integer and just use the date and NOT the Time
I tried to use
=COUNTIFS($C$2:$C$6,">="&C2,$C$2:$C$6,"<"&C2+1,$A$2:$A$6,A2), which should count the Dates and not time, but did not work
Hence the Helper column in D
=IF(OR(C2="Do Not Disturb", COUNTIFS(Sheet1!$A$2:$A$6,Sheet2!$A2,Sheet1!$B$2:$B$6,"pending")>0,COUNTIFS(Sheet1!$A$2:$A$6,A2,Sheet1!$D$2:$D$6,Sheet1!D2,Sheet1!$B$2:$B$6,"completed")>1),"not Available","Available")
I suspect other members may help, and possibly using SUMPRODUCT() - but i could not get that to work either
Hi Etaf,
Time is not an issue. Just considering the date is enough.
The 1st and 2nd point is working perfectly.
However, regarding my 3rd point, I see that although D has 2 completed statuses on 31-12-2021, i.e. not today's date, yet it is Not Available. Where in fact in today's date it should be available because the Not Available for 2 completed statuses should be active only for today's date and note for any past date.
To simplify my statement- Only if a person has 2 completed statuses in today's date then he should be Not Available. For any past dates irrespective of any number of completed status, person should be Available in today's date.
Any solution to this?
Thank you for your time and timely response.
how about
=IF(OR(C2="Do Not Disturb", COUNTIFS(Sheet1!$A$2:$A$10,Sheet2!$A2,Sheet1!$B$2:$B$10,"pending")>0,COUNTIFS(Sheet1!$A$2:$A$10,A2,Sheet1!$D$2:$D$10,TODAY(),Sheet1!$B$2:$B$10,"completed")>1),"Not Available","Available")
Perfect.
Everything working exactly as I wanted.
Thanks a ton for your time and effort.
you are welcome
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks