Status Balls formula help

Status Balls formula help

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

 

 

 

 

 

status ball.jpg

Tagged:

Comments

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

Sign In or Register to comment.