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
-
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 Technology - Smartsheet Solution Partner.
-
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 Technology - 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?
-
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 - Smartsheet Solution Partner.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!