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 ...

Formulas & Functions

Need Help

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

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.

Forum Discussion

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