# IF statement using multiple criteria - tweaking required

#### Kelli Webb

##### Member
Hi,

I'm hoping you may be able to assist. I have given this one a good go, but I'm still falling short
1. if Joined column D = pos start column H And is >= 1/4/2019 but <= 31/3/2020 -------- result would be "New"; Or
2. if the Staff Member column A is the sames as the next row And there is a change in Position column G ------- the result would be "Yes"
3. if neither of the above criteria is met ------ the result would be "No"
Column J - My formula that gives some results
Column K - is what the results should be
Column M - provides why my result differs i.e. why my formula is wrong

thank you and take care,

#### Attachments

• 13.5 KB Views: 5

#### rahulshewale1

##### Active Member
hi @Kelli Webb ,

Test this ?

=IF(AND(D2>=DATE(2019,4,1),D2<=DATE(2019,3,2020)),"New",IF(AND(B2=B3,G2<>G3),"Yes","No"))

i am not clear your 1st Condition

#### Attachments

• 15.4 KB Views: 2

#### pecoflyer

##### Active Member
@rahulshewale1
There seems to be a typo in your formula
=IF(AND(D2>=DATE(2019,4,1),D2<=DATE(2020,3,31)),"New",IF(AND(B2=B3,G2<>G3),"Yes","No"))
(I have no idea if it works, though)

#### rahulshewale1

##### Active Member
hi @Kelli Webb ,

try this Revised?

=IF(AND(D2>=DATE(2019,4,1),D2<=DATE(2020,3,31)),"New",IF(AND(B2=B3,G2<>G3),"Yes","No"))

#### Attachments

• 15.4 KB Views: 2

#### Kelli Webb

##### Member
hi @Kelli Webb ,

Test this ?

=IF(AND(D2>=DATE(2019,4,1),D2<=DATE(2019,3,2020)),"New",IF(AND(B2=B3,G2<>G3),"Yes","No"))

i am not clear your 1st Condition
Hi Rahul,

You were so close and gave me enough for me to add in the missing condition you will see I've highlighted in red at the beginning of the formula, and I just needed to update the last condition to look at Column A - I also had to fix up some dates.

=IF(AND(\$D2=\$H2,\$H2>=DATE(2019,4,1),\$H2<=DATE(2019,3,2020)),"New",IF(AND(\$A2=\$A3,\$G2<>\$G3),"Yes","No"))

thank you so much, you guys are geniuses

I hope all stays well in your part of the world and stay safe!

#### Attachments

• 15.5 KB Views: 3
Last edited:

#### rahulshewale1

##### Active Member
Hi Rahul,

You were so close and gave me enough for me to add in the missing condition you will see I've highlighted in red at the beginning of the formula, and I just needed to update the last condition to look at Column A - I also had to fix up some dates.

=IF(AND(\$D2=\$H2,\$H2>=DATE(2019,4,1),\$H2<=DATE(2019,3,2020)),"New",IF(AND(\$A2=\$A3,\$G2<>\$G3),"Yes","No"))

thank you so much, you guys are geniuses

I hope all stays well in your part of the world and stay safe!