Hello, I'm trying to do a formula to match 3 different criteria to get a yes or no answer. It is not working out, possibly because several of the cells I am working with are already formulas.
The question is basic; is there a line on a specific date, for a specific job code for AM. This is the formula that I'm trying to use:
This is the sample data that I am looking for the information in (Monday the response would be NO and the other 2 days YES):
I have tried to refer to both column B and column R for the dates thinking that was causing the problem but same results regardless which of those 2 columns are referenced. Can someone assist with this? Thanks in advance.
The question is basic; is there a line on a specific date, for a specific job code for AM. This is the formula that I'm trying to use:
PP11-2022 Reuben's (2020-05-16 to 2022-05-29).xlsx | ||||
---|---|---|---|---|
A | B | |||
3 | Date | BARTENDER | ||
4 | Mon-16-May-22 | NO | ||
5 | Tue-17-May-22 | NO | ||
6 | Wed-18-May-22 | NO | ||
7 | Thu-19-May-22 | NO | ||
8 | Fri-20-May-22 | NO | ||
9 | Sat-21-May-22 | NO | ||
10 | Sun-22-May-22 | NO | ||
11 | Mon-23-May-22 | NO | ||
12 | Tue-24-May-22 | NO | ||
13 | Wed-25-May-22 | NO | ||
14 | Thu-26-May-22 | NO | ||
15 | Fri-27-May-22 | NO | ||
16 | Sat-28-May-22 | NO | ||
17 | Sun-29-May-22 | NO | ||
A-Support Staff |
Cell Formulas | ||
---|---|---|
Range | Formula | |
A4 | A4 | =SUMMARY!$J$1 |
B4:B17 | B4 | =IF(AND('Punch Report'!$R:$R=$A4,'Punch Report'!$H:$H="BARTENDER",'Punch Report'!$W:$W="AM"),"YES","NO") |
A5:A17 | A5 | =A4+1 |
This is the sample data that I am looking for the information in (Monday the response would be NO and the other 2 days YES):
PP11-2022 Reuben's (2020-05-16 to 2022-05-29).xlsx | |||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | H | R | W | |||||||||||||||||||||
1 | Employee ID | Date | Role | Date | Shift | ||||||||||||||||||||
12 | 7CN6000000050 | Tue-17-May-22 | BARTENDER | 44698 | AM | ||||||||||||||||||||
28 | 7CN6200000085 | Tue-17-May-22 | BARTENDER | 44698 | PM | ||||||||||||||||||||
45 | 7CN6000000024 | Wed-18-May-22 | BARTENDER | 44699 | AM | ||||||||||||||||||||
73 | 7CN6000000050 | Wed-18-May-22 | BARTENDER | 44699 | AM | ||||||||||||||||||||
Punch Report |
Cell Formulas | ||
---|---|---|
Range | Formula | |
R12,R73,R45,R28 | R12 | =LEFT(B12,5) |
W12,W73,W45,W28 | W12 | =IF(V12<0.583333333333333,"AM",IF(V12>=1,"AM","PM")) |
I have tried to refer to both column B and column R for the dates thinking that was causing the problem but same results regardless which of those 2 columns are referenced. Can someone assist with this? Thanks in advance.