Status Balls formula help

Dana Wanner
Dana Wanner ✭✭
edited 12/09/19 in Formulas and Functions

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

Tags:

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!