Traffic Light System for Due Dates

USERSSME

Hi,

I was wondering if someone could help me with a formula. I'm looking to do a traffic light column for the following rules. Hopefully I've made this clear, if not any suggestions would be appreciated.

Our products (Panels) take 7 days to arrive from manufacture.

If ( [10] Panel Despatch ) + 7 days is EARLIER than the [012] Client PC Date = Green Traffic light in "Meet PC Date' Column -

IF its LATER than [012] Client PC Date = Red Traffic Light

Our Panels also have a 7 week lead time (Working Days) -

(If there is no ([10] Panel Despatch Date) yet then -

IF [012] Client PC Date is within 7 weeks OR MORE - Green Traffic Light in "Meet PC Date"

IF [012] Client PC Date is within 6 weeks - Yellow Traffic Light in "Meet PC Date"

IF [012] Client PC Date is within 5 weeks - Red Traffic Light in "Meet PC Date"

Would appreciate any assistance.

Any questions please let me know.

Thanks

Gia Thinh

Hi @USERSSME,

Try this formula. Hope it works.

=IF([Panel Despatch]@row <> ""; IF([Panel Despatch]@row + 7 < [Client PC Date]@row; "Green"; "Red"); IF((WEEKNUMBER([Client PC Date]@row) - WEEKNUMBER(TODAY())) <= 5; "Red"; IF((WEEKNUMBER([Client PC Date]@row) - WEEKNUMBER(TODAY())) <= 6; "Yellow"; IF((WEEKNUMBER([Client PC Date]@row) - WEEKNUMBER(TODAY())) >= 7; "Green"))))

Gia Thinh

Hi @USERSSME,

Just replace ";" with "," for regional using. Try this formula. Hope it works.

=IF([Panel Despatch]@row <> "", IF([Panel Despatch]@row + 7 < [Client PC Date]@row, "Green", "Red"), IF((WEEKNUMBER([Client PC Date]@row) - WEEKNUMBER(TODAY())) <= 5, "Red", IF((WEEKNUMBER([Client PC Date]@row) - WEEKNUMBER(TODAY())) <= 6, "Yellow", IF((WEEKNUMBER([Client PC Date]@row) - WEEKNUMBER(TODAY())) >= 7, "Green"))))

USERSSME

Hi,

Thank you so much, it half works the way i need it too. If there's no date in the Client PC date and or there is no panel Despatch date - is there anyway I can return a blank square rather than a red circle?

Appreciate your help!

USERSSME

Or a Grey Circle?

Gia Thinh

Just add a condition checking of Client PC date blank or not right before existing formula:

=IF([Client PC Date]@row = "", "[]", IF([Panel Despatch]@row <> "", IF([Panel Despatch]@row + 7 < [Client PC Date]@row, "Green", "Red"), IF((WEEKNUMBER([Client PC Date]@row) - WEEKNUMBER(TODAY())) <= 5, "Red", IF((WEEKNUMBER([Client PC Date]@row) - WEEKNUMBER(TODAY())) <= 6, "Yellow", IF((WEEKNUMBER([Client PC Date]@row) - WEEKNUMBER(TODAY())) >= 7, "Green")))))