Visual Status bar with dates

Michael Halvey

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

Frank Falco

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

Frank Falco

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

Michael Halvey

@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?

Frank Falco

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

Michael Halvey

@Frank Falco - Worked perfectly! Thank you so much for your time!

Frank Falco

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.