Multiple If Statements - Harvey Ball

so_tx_anglerette

Hello! I need to evaluate 4 dates to determine the color of a harvey ball (red, yellow, green, or blue). The metric is used to determine if equipment has shipped at least 2 weeks prior to need by date and has been delivered at least 5 days prior to Need by Date. The harvey ball will reside in the "Equipment Status" column. I am a die-hard SS user but I am stumped. Appreciate the help!

Date Fields: "Install Date" / "Need by Date" / "Actual Ship Date" / "Actual Delivery Date"

Blue - Need by Date is blank.

Blue - Install date is in the past and equipment has been delivered.

Green - Actual Delivery Date is 5+ days prior to Need by Date.

Green – Actual Ship Date is date; Actual Delivery Date is blank, Need by Date greater than 10 days.

Yellow - Actual Ship Date is date, Actual Delivery Date is blank, Need by Date within 6 days.

Yellow - Actual Ship Date is blank and Need by Date is within 13 days.

Red - Actual Ship Date is a date, Actual Delivery Date is blank and within 5 days of Need by Date.

Red – Actual Ship Date is blank, and Need by Date is within 10 days.

Nick Korna

Hi @so_tx_anglerette,

I am working on this, but can you clarify something for me as I think a couple of your conditions are overlapping on yellow/red:

Yellow - Actual Ship Date is date, Actual Delivery Date is blank, Need by Date within 6 days.

Red - Actual Ship Date is a date, Actual Delivery Date is blank and within 5 days of Need by Date.

I am unsure if I am misunderstanding something with these 2 as anything qualifying for the latter will be covered by the former.

Even if we put the Red condition before the Yellow, this yellow condition will only mean there is 1 day that would qualify for.

Please can you point out if I've misunderstood something or if the conditions should be amended slightly.

so_tx_anglerette

Hello Nick! I think it makes sense to omit the "RED" condition in this instance.

Nick Korna

I've made a slight adjustment so if Need By Date is in the past, you get a red result. There is one set of circumstances (at least) seemingly left over, which is where the actual ship date is blank and need by date is over 13 days.

Formula:

=IF(OR(AND([Actual Delivery Date]@row <> "", [Install Date]@row <> "", [Install Date]@row < TODAY()), [Need By Date]@row = ""), "Blue", IF(OR(AND(([Need By Date]@row - [Actual Delivery Date]@row) >= 5, [Need By Date]@row > TODAY()), AND([Actual Ship Date]@row <> "", [Need By Date]@row - TODAY() >= 10)), "Green", IF(AND([Actual Ship Date]@row = "", ([Need By Date]@row - TODAY()) < 10), "Red", IF(OR(AND([Actual Ship Date]@row <> "", [Actual Delivery Date]@row = "", [Need By Date]@row - TODAY() < 7, [Need By Date]@row - TODAY() > 0), ([Need By Date]@row - TODAY() <= 13)), "Yellow"))))

I would check if this gives what you expect and point out anything else that may crop up as an exception!

so_tx_anglerette

Hi Nick! The blue seems to function well (yeah!), however, the green, red and yellow aren't. On the attachment, the first row should show red, the second row should show green and the fifth row should reflect green too. I really appreciate your help.

Inventory Metric 1.17.24.jpg