Sign in to join the conversation:
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
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)
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
=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
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)
Good suggestion, Mike. Thanks. I read "on time" as "same as" and went from there.
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))
Another good suggestion. That's what I get for not plugging it in to an actual sheet for testing. Haha.
Hello, I have a general question. looking to move a SHEET from one workspace to another. That sheet has a FORM and Workflow Alerts set up. Will that disrupt my workflow and inactivate the form link? Just want to confirm what I need to do to ensure the SHEET and workflow alerts don't break. User is the same. Second question…
I have a couple timeline widgets tied to reports on my dashboard, just for tracking rentals of different types and having the length of the rental visible on the timeline. As of this morning, every single one shows the error "Columns used to configure this widget have been deleted or changed. Edit this widget to update…
I am attempting to use a timeline from a portfolio report on my dashboard but after setting it up I keep receiving an error - Columns used to configure this widget have been deleted or changed. I have took all filters and grouping off of my report and it still showing this error. I don't know how I can get around this…
