• 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

  • Chandoo Movements.xlsx
    13.5 KB · Views: 5
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

  • Chandoo Movements.xlsx
    15.4 KB · Views: 2
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

  • Chandoo Movements v2.xlsx
    15.5 KB · Views: 4
Last edited:
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
 
Back
Top