• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

IF statement using multiple criteria - tweaking required

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

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!

Love your work, Kelli
 

Attachments

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!

Love your work, Kelli
Welcome
 
Top