Multiple IF statements & Multiple Results

[Will_iam]

Hi,
I need help with a formula.

=IF($O5<>"Complete",IF(AND($S5<$B$1,$S5>=DATE(2012,10,1),$R5<=DATE(2012,10,31)),"Late"," "))*IF($O5="Running",IF(AND($B$1<=DATE(2012,10,31)),"OT",""))

This is what I have, and it currently just returns #Value.
Cell O5 contains various statuses.
Cell S5 contains a start date, R5 a completion date and B1 todays date.

Both formulas work on there own i.e.
=IF($O5<>"Complete",IF(AND($S5<$B$1,$S5>=DATE(2012,10,1),$R5<=DATE(2012,10,31)),"Late"," "))

And
IF($O5="Running On Time",IF(AND($B$1<=DATE(2012,10,31)),"OT",""))

But I would like them to both work for one cell.

Any ideas?
Cheers

[Sindhus]

Try combining your formula this way.

Please Login or Register  to view this content.

Since <>complete includes running, i have added running as the first condition. If its not running, your <>complete condition will run

[Richard Buttrey]

Hi,

Your first formula returns a space character (" ") if either of the two elements of the formula either side of multiplier * return False.
You can't multiply non numeric values.

Change the " " to a zero.

[Will_iam]

@Sindhus


I knew it would be something pretty simply. But sometimes you just need someone else to point it out! Thank you very much. Works perfectly!