# Traffic Light System for Due Dates

Options
✭✭

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

• ✭✭✭✭✭✭
Options

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 Technology Co., LTD - Smartsheet Solution Partner.

• ✭✭✭✭✭✭
Options

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"))))

Gia Thinh Technology Co., LTD - Smartsheet Solution Partner.

• ✭✭
Options

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?

• ✭✭
Options

Or a Grey Circle?

• ✭✭✭✭✭✭
edited 03/18/22
Options

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")))))`

Gia Thinh Technology Co., LTD - Smartsheet Solution Partner.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!