Forum Discussion

Keira Higgins's avatar
Keira Higgins
Copper Contributor
Feb 23, 2017

Im not sure why this function isn't working..

I am trying to link entered start and end dates into a calendar timeline... i am able to get it to work for one set of start/end dates with the originally planned and the actual dates...but then i cannot add a second plot of the original/planned dates.....Which is why i was trying to use AND(function I used to work for one set, same function for the other set) ... they work separately, but apparently not together..

 

Please and thank you!

Formulas & Functions
Need Help

3 Replies

  • Keira Higgins's avatar
    Keira Higgins
    Copper Contributor
    Feb 23, 2017

    =If(AND(IF(AND(DY$5>=$X8,DY$5<=$Y8),$W$6,if(and(DY$5>=$AA8,DY$5<=$AB8),"")),if(and(DY$5>=$H8,DY$5<=$I8),$G$6,If(and(DY$5>=$K8,DY$5<=$L8),""))))

     

    *This is the function, sorry, I thought a picture posted*

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Feb 23, 2017

      Hi Keira,

       

      Sorry, didn't catch the business logic, but your formula

      =IF(
	AND(
		IF(
			AND(DY$5>=$X8,DY$5<=$Y8),
			$W$6,
			IF(
				AND(DY$5>=$AA8,DY$5<=$AB8),
				""
			)
		),
		IF(
			AND(DY$5>=$H8,DY$5<=$I8),
			$G$6,
			If(
				AND(DY$5>=$K8,DY$5<=$L8),
				""
			)
		)
	)
)

      shall not work in any case. On top level it looks like

      =IF(AND(Value1,Value2))

      Depends on the data logic you may use nested IF

      =IF(
	AND(DY$5>=$X8,DY$5<=$Y8),
	$W$6,
	IF(
		AND(DY$5>=$AA8,DY$5<=$AB8),
		"",
		IF(
			AND(DY$5>=$H8,DY$5<=$I8),
			$G$6,
			IF(
				AND(DY$5>=$K8,DY$5<=$L8),
				""
			)
		)
	)
)

      which actually means

      =if Condition1 then Result1 else
	if Condition2 then Result2 else
		if ...
		...
 else Nothing

      Skip for a while Excel notation, formulate your logic in plain English as above, convert to Excel formulas then.