Traffic Light System for Due Dates

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

Answers

  • Gia Thinh
    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 Co. - Smartsheet Solution Partner.

  • Gia Thinh
    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"))))



    Gia Thinh Co. - Smartsheet Solution Partner.

  • 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!



  • Or a Grey Circle?

  • Gia Thinh
    Gia Thinh ✭✭✭✭✭✭
    edited 03/18/22

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!