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
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")
-
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
Categories
Check out the Formula Handbook template!