Visual Status bar with dates
Hello - First time posting here! 😁
My question is I am trying to create a formula that will populate the visual status bar and show progress as dates are entered. So if no dates are entered, the Fulfillment Status is "empty." If the Date 1 column has a date, then the Fulfillment Status is now a "Quarter" full and so on with the process until "Full". Is there an "IF" statement that will work with this?
Any help on this would be great! Thank you ahead of time.
-Michael
Best Answer
-
Replace the =COUNTIF([Date 1]@row:[Date 4]@row, ISDATE(@cell)) with
=COUNTIF([Fulfillment Received]@row:[Photography Complete]@row, 1) + COUNTIF([Fulfillment packing date]@row, ISDATE(@cell))
Assuming the * and checkbox columns are next to each other. Alternatively you can check each one individually and add them together.
=COUNTIF([Fulfillment Received]@row,1)+COUNTIF[Photography Complete]@row, 1)+COUNTIF[QA Received]@row, 1)+COUNTIF([Fulfillment packing date]@row, ISDATE(@cell))
✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
Answers
-
Try this
Create a column, which you can hide, to count the number if columns are dates
=COUNTIF([Date 1]@row:[Date 4]@row, ISDATE(@cell))
This counts the number of columns that are dates
Then in the fulfilment Status column put this nested IF statement
=IF([Primary Column]@row = 1, "Quarter", IF([Primary Column]@row = 2, "Half", IF([Primary Column]@row = 3, "Three Quarter", IF([Primary Column]@row = 4, "Full", "Empty"))))
✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
-
@Frank Falco Thank you so much for taking the time. It works perfectly! The department I am building this out for came back to me just now saying they don't want it to be all to just be dates.
They are looking for a drop down column, then 2 stars and a check box and lastly a date. I have attached another photo to show the example. Any thoughts on a formula or workaround for that?
-
Replace the =COUNTIF([Date 1]@row:[Date 4]@row, ISDATE(@cell)) with
=COUNTIF([Fulfillment Received]@row:[Photography Complete]@row, 1) + COUNTIF([Fulfillment packing date]@row, ISDATE(@cell))
Assuming the * and checkbox columns are next to each other. Alternatively you can check each one individually and add them together.
=COUNTIF([Fulfillment Received]@row,1)+COUNTIF[Photography Complete]@row, 1)+COUNTIF[QA Received]@row, 1)+COUNTIF([Fulfillment packing date]@row, ISDATE(@cell))
✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
-
@Frank Falco - Worked perfectly! Thank you so much for your time!
-
I just noticed you also had a drop down column [Manufacturing Status].You did not state what the values are in that so I am not sure how you want to include that in the calculation. I will leave that with you to work on. Let me know if you have any issues.
✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!