Status Balls formula help

Dana Wanner

Hi,

I am very new to this an am getting a bit frustrated.  I have the following columns, Ship Date, Fabric Order Date, Fabric ETA, and Fabric Received or In Stock.  The first 3 columns are date columns and the 3rd is a checkbox.

I would like my status balls to due the following:

If Fabric Order Date is blank, I would like a blue ball

If the Fabric ETA has a date 3 days before the Ship Date or closer, I would like a red ball, otherwise I would like a yellow ball.

If the Fabric Received or In Stock box is checked, I would like a green ball.

Is this possible to do?

This is what I have come up with so far.  

=IF([Fabric ETA]1 + 3 <= [Ship Date]1, IF([Fabric Order Date]1 = "", "Blue", "Yellow"), "Red")

But it does not make my ball turn green if the Fabric Received or In Stock is checked. --Sometimes a fabric is already in stock and that would be checked first.

Thank you much for any help that can be offered.

Thanks,

Dana

 

 

 

 

 

Dana Wanner

I got it to work using this....

=IF([Fabric Received or In Stock]1 <> 1, IF(OR([Ship Date]1 = "", [Fabric ETA]1 + 3 <= [Ship Date]1), IF([Fabric Order Date]1 = "", "Blue", "Yellow"), "Red"), "Green")

Chris McKay

Well done! Kind of moot now, but this is how I would have done it:

=IF(ISBLANK([Fabric Order Date]1), "Blue", IF([Fabric Received or In Stock]1, "Green", IF(OR([Fabric ETA]1 +3 <= [Ship Date]1, ISBLANK([Ship Date]1)), "Red", "Yellow")))

I tend to find that writing IF statements that meet your list of requirements (rather than what they aren't) is easier to manage.