Welcome!

count "on time" dates

Nino

hi there,

I am trying to count all dates that are on time comparing my due date column and delivery date column...

tried countifs, but it wont let me compare the whole columns...

please help.

Thanks

nino

Screen Shot 2019-01-03 at 16.35.54.png

Find more posts tagged with

Project Management

Comments

Paul Newcome

You'll need a helper column. In this example I'll make it a checkbox, but you can use whatever works best for you. In the checkbox column use an IF statement to compare the two dates and check the box if they are the same.

=IF([First Date Column]@row = [Second Date Column]@row, 1)

You can then use a COUNTIFS on the helper column to count your checkboxes.

=COUNTIFS([Helper Column]:[Helper Column], 1)

L_123

This was fun.

=COUNT(COLLECT(COLLECT(Start:End, Start:End, ISDATE(@cell)), COLLECT(Start:End, Start:End, ISDATE(@cell)), AND(INDEX(COLLECT(Start:End, Start:End, ISDATE(@cell)), MATCH(@cell, COLLECT(Start:End, Start:End, ISDATE(@cell)), true)) > INDEX(COLLECT(Start:End, Start:End, ISDATE(@cell)), MATCH(@cell, COLLECT(Start:End, Start:End, ISDATE(@cell)), true) + 1), MATCH(@cell, COLLECT(Start:End, Start:End, ISDATE(@cell)), true) / 2 = ROUND(MATCH(@cell, COLLECT(Start:End, Start:End, ISDATE(@cell)), true) / 2))))

Not often you get the chance to do a nested collect.

I recommend pauls solution however as mine can't handle duplicates in the dates

Also the start column has to be directly to the left of the end column or it will break

L_123

=COUNT(COLLECT(COLLECT([Due Date]:Delivered, [Due Date]:Delivered, ISDATE(@cell)), COLLECT([Due Date]:Delivered, [Due Date]:Delivered, ISDATE(@cell)), AND(INDEX(COLLECT([Due Date]:Delivered, [Due Date]:Delivered, ISDATE(@cell)), MATCH(@cell, COLLECT([Due Date]:Delivered, [Due Date]:Delivered, ISDATE(@cell)), true)) > INDEX(COLLECT([Due Date]:Delivered, [Due Date]:Delivered, ISDATE(@cell)), MATCH(@cell, COLLECT([Due Date]:Delivered, [Due Date]:Delivered, ISDATE(@cell)), true) + 1), MATCH(@cell, COLLECT([Due Date]:Delivered, [Due Date]:Delivered, ISDATE(@cell)), true) / 2 = ROUND(MATCH(@cell, COLLECT([Due Date]:Delivered, [Due Date]:Delivered, ISDATE(@cell)), true) / 2))))

Heres a copy that uses your column names

Mike Wilday

I recommend adding the helper column as well. But you should modify Paul's formula for the helper column slightly to account for dates earlier than due dates.

=IF([Delivered]@row <= [Due Date]@row, 1)

Paul Newcome

Good suggestion, Mike. Thanks. I read "on time" as "same as" and went from there.

Brian W

I would also consider adding a check for blanks, because in this formula blanks will show up as as "on time".

=IF(NOT(ISBLANK(Delivered@row)), IF(Delivered@row <= [Due Date]@row, 1))

Paul Newcome

Another good suggestion. That's what I get for not plugging it in to an actual sheet for testing. Haha.

Sign in to join the conversation!

Quick Links

Trending discussions

Delete
How can I delete old sheets I no longer use from my account?
How do I get a dashboard chart based on a report to display as a donut chart?
I'm trying to display a task progress chart showing "complete", "late", and "not complete" in my dashboard and for some reason the chart will display as a column chart but it won't display as a donut chart even though I'm referencing the same report. I'd prefer to display as a donut chart as I think it looks better and is…
Keep getting Selected data cannot be charted. Please select a new range.
Hello, I am trying to create a chart widget in my dashboard, but I keep getting "Selected data cannot be charted. Please select a new range." error. The report I am using is already summarized in numeric value. What is funny is I have report that I created a chart widget in another dashboard, and when I use that same…