Date Counter for Weekly Ontime Rate

Jennifer Ford

Hi there!

I need some help coming up with two formulas that will count how many times in the week we hit our projected due date and how many times we are early. I already have a hidden column that looks at the date and calculates the week of the year.

 

I need the fomulas to read something like:

count if 'date completed' is = 'due date' for "W29"

count if 'date completed' is < 'due date' for "W29"

 

Tried to use some of the existing formulas and minipulate, but they did not work for me. The counter will be connected to another sheet what will give me our % ontime rate.

 

Below is a screenshot of all my sheet counters that I have so far.

 

Thank you for looking at this for me!

 

 

Brett Evans

Try this:

 

https://app.smartsheet.com/b/publish?EQBCT=c3b5dedb164946eeae1e4bc9c6a39035

 

The summary column on the left is doing the math and stating if something is early late or on time.  You should be able to hover on any column to see formulas.

Jennifer Ford

Thank you  Brett!

That was helpful. I was able to use your formula as a base and tweaked it a bit. Instead of using 3 columns for counting, we used one row and assigned the colored harvey balls. Then, depending on the color we assigned it, we attached it to a cell to count.

 

Yay!!!

Brett Evans

Glad it helped,