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.
