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'm working through the writing formulas optional activity and in the 'Nested IF function - Progress Column' task it directs you to put in the following formula '=IF([% Complete]@row = 0, “Empty”)'. However, when I put this formula into the spreadsheet, '04a_Marketing Request Tracker - Formulas', I get an…
Have any of you created a Smartsheet that would show the savings for a project by the month? In other words, if I saved $1200 annually and the project ended in say March, then the savings would go $120 per month, Mar 2025-Feb 2026. I have done it on Excel sheets manually, but I think there should be a way to do it with…
I have had approval workflows and notifications built into a couple sheets for about a year now. In the past month, I am getting a lot of feedback saying people did not receive the email request for approval or notification email. It isn't all the time, just sometimes and there does not seem to be a pattern on which ones…
