Counting Dates within a Date column

Gerhard da Costa Pinto

Hi all,

I'm trying to set up a formula that counts specific dates within a date column, using the following approach:

=COUNTIF({Reference 1}, "31-12-2020")

It keeps returning 0 when there are least 4 dates within the target column that match the specified criteria.

Can anyone advise how I can fix this?

Many thanks in advance!

Bassam Khalil

Hi @Gerhard da Costa Pinto

Is it allowed to you create new sheet, if so you can create summary sheet to make all the calculation you need and use a reference for the data you need in your calculation the structural source data sheet.

Debbie Sawyer

This works for me...

你绝对相信你什么呢is exactly what you have on your sheet, as I can't see anything wrong with your formula! :D

The reference seems to have changed from {REFERENCE1} to {Portfolio Plan Range 3} this is the same date column? or if it is different, clarify that it is a date column that you are pointing to and that you have set up the range in the current sheet and not just typed it in from another formula on a different sheet? (Each sheet need the cross sheet references re-defining - this would put up an unparseable error if the range had been copied from another sheet and not redefined)

=COUNTIFS({Portfolio Plan Range 3}, ISDATE(@cell), {Portfolio Plan Range 3}, AND(DAY(@cell) = 31, MONTH(@cell) = 12, YEAR(@cell) = 2020))

Try copying and pasting this one in to your sheet. (ensuring {Portfolio Plan Range 3} was defined and named on the current sheet.

Pop a screen shot up on it in place if you are still getting unparseable.

Good luck

Debbie

Bassam Khalil

Hi @Gerhard da Costa Pinto

Hope you are fine, your problem is when you define the criteria in count formula as a text so the result for sure will be 0, to solve this problem create help column for the criteria and input in that column the date you need to count then the formula will work correctly, i create for you a sample please check the following screen shot:

1- Reference column & Date Column are Date type.

2- Count column is Text/Number type

3- the formula in count column =COUNTIFS([Date Column]:[Date Column], Reference@row)

Gerhard da Costa Pinto

Hi@Bassam.M Khalil,

I'm very well thank you, I hope you are too. Thank you for replying to me and for your clarification. Unfortunately, due to the structural requirements of the source data, I cannot input helper columns.

Is there any other way I can work around this? I tried running a report isolating the dates in question, so that I could run a simple =COUNT formula, but it seems Smartsheet doesn't consider Reports to be referenceable.

Many thanks,

Gerhard

Bassam Khalil

Hi @Gerhard da Costa Pinto

Is it allowed to you create new sheet, if so you can create summary sheet to make all the calculation you need and use a reference for the data you need in your calculation the structural source data sheet.

Gerhard da Costa Pinto

嗨@Bassam.M Khalil,

I had thought of this option, but I was hoping to avoid duplication of data and use of multiple data sources.

Regardless, thank you again for your time and help, it is much appreciated!

Kind regards,

Gerhard Costa Pinto

Bassam Khalil

Hi @Gerhard da Costa Pinto

You are welcome

Andrée Starå

Hi@Gerhard da Costa Pinto

To add to Bassam's excellent advice/answer.

You could add a helper date field in the Sheet Summary section and reference that in the formula instead.

Would that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå| Workflow Consultant / CEO @WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community bymarking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Debbie Sawyer

@Gerhard da Costa Pinto

Hi

I haven't visited the community for a while and I appreciate you have an accepted answer on this - I just wanted to add that you can achieve this without helper columns if you wanted to. I have created a little example for you. It works for me.

Hope this helps.

Kind regards

Debbie

Gerhard da Costa Pinto

Hi@Debbie Sawyer,

Thank you so much for getting back to me and for your suggestion. I've just tried your suggestion and got an #UNPARSEABLE error.

Have I missed something? Please note, that I am referencing another sheet, butREFERENCE 1in the formula below is the date column in question.

=COUNTIF({REFERENCE 1},AND((DAY(@cell)=31,Month(@cell)=12,Year(@cell)=2020))

Kind regards,

Gerhard

Debbie Sawyer

Hi@Gerhard da Costa Pinto

I believe you may have an extra ( in your formula...Try this

=COUNTIF({REFERENCE 1},AND(DAY(@cell)=31,Month(@cell)=12,Year(@cell)=2020))

Debbie Sawyer

@Gerhard da Costa Pinto

Just realised, if your cross sheet reference is referring to a column where there might be unfilled cells (Blank cells) then you might get an Invalid data type error return.

This formula fixes that:

=COUNTIFS({REFERENCE 1}, ISDATE(@cell), {REFERENCE 1}, AND(DAY(@cell) = 15, MONTH(@cell) = 12, YEAR(@cell) = 2020))

Hope this helps!

Kind regards

Debbie

Gerhard da Costa Pinto

@Gerhard da Costa Pinto<\/a> <\/p>

Just realised, if your cross sheet reference is referring to a column where there might be unfilled cells (Blank cells) then you might get an Invalid data type error return.<\/p>

This formula fixes that:<\/p>

=COUNTIFS({REFERENCE 1}, ISDATE(@cell), {REFERENCE 1}, AND(DAY(@cell) = 15, MONTH(@cell) = 12, YEAR(@cell) = 2020))<\/p>

Hope this helps! 🤩<\/span><\/p>

Kind regards<\/p>

Debbie<\/p>","bodyRaw":"[{\"insert\":{\"mention\":{\"name\":\"Gerhard da Costa Pinto\",\"userID\":129489}}},{\"insert\":\" \\nJust realised, if your cross sheet reference is referring to a column where there might be unfilled cells (Blank cells) then you might get an Invalid data type error return.\\nThis formula fixes that:\\n=COUNTIFS({REFERENCE 1}, ISDATE(@cell), {REFERENCE 1}, AND(DAY(@cell) = 15, MONTH(@cell) = 12, YEAR(@cell) = 2020))\\nHope this helps! \"},{\"insert\":{\"emoji\":{\"emojiChar\":\"🤩\"}}},{\"insert\":\"\\nKind regards\\nDebbie\\n\"}]","format":"rich","dateInserted":"2020-12-08T10:42:11+00:00","insertUser":{"userID":80301,"name":"Debbie Sawyer","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Debbie%20Sawyer","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/2019-02\/6f\/d1\/n6fd11e91b71000f2b152e84c8f072631.jpg","dateLastActive":"2023-08-06T09:27:03+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/271002#Comment_271002","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/271002#Comment_271002

Hi Debbie,

Thank you again for getting back to me! You were right in that the column being referenced has blank cells in it, however the proposed formula to solve this comes back as #UNPARSEABLE.

I'm fairly certain I got everything exactly as your formula, so I'm not sure why this isn't working.

=COUNTIFS({Portfolio Plan Range 3},ISDATE(@cell),{Portfolio Plan Range 3},AND(DAY(@cell)=31,MONTH(@cell)=12,Year(@cell)2020))

Any ideas?

Kind regards,

Gerhard

Debbie Sawyer

This works for me...

你绝对相信你什么呢is exactly what you have on your sheet, as I can't see anything wrong with your formula! :D

The reference seems to have changed from {REFERENCE1} to {Portfolio Plan Range 3} this is the same date column? or if it is different, clarify that it is a date column that you are pointing to and that you have set up the range in the current sheet and not just typed it in from another formula on a different sheet? (Each sheet need the cross sheet references re-defining - this would put up an unparseable error if the range had been copied from another sheet and not redefined)

=COUNTIFS({Portfolio Plan Range 3}, ISDATE(@cell), {Portfolio Plan Range 3}, AND(DAY(@cell) = 31, MONTH(@cell) = 12, YEAR(@cell) = 2020))

Try copying and pasting this one in to your sheet. (ensuring {Portfolio Plan Range 3} was defined and named on the current sheet.

Pop a screen shot up on it in place if you are still getting unparseable.

Good luck

Debbie

Gerhard da Costa Pinto

This works for me...<\/p>

Are you absolutely sure that what you put on here is exactly what you have on your sheet, as I can't see anything wrong with your formula! :D<\/p>

The reference seems to have changed from {REFERENCE1} to {Portfolio Plan Range 3} this is the same date column? or if it is different, clarify that it is a date column that you are pointing to and that you have set up the range in the current sheet and not just typed it in from another formula on a different sheet? (Each sheet need the cross sheet references re-defining - this would put up an unparseable error if the range had been copied from another sheet and not redefined)<\/p>

=COUNTIFS({Portfolio Plan Range 3}, ISDATE(@cell), {Portfolio Plan Range 3}, AND(DAY(@cell) = 31, MONTH(@cell) = 12, YEAR(@cell) = 2020))<\/p>

Try copying and pasting this one in to your sheet. (ensuring {Portfolio Plan Range 3} was defined and named on the current sheet.<\/p>

Pop a screen shot up on it in place if you are still getting unparseable.<\/p>

Good luck<\/p>

Debbie<\/p>","bodyRaw":"[{\"insert\":\"This works for me...\\nAre you absolutely sure that what you put on here is exactly what you have on your sheet, as I can't see anything wrong with your formula! :D\\n The reference seems to have changed from {REFERENCE1} to {Portfolio Plan Range 3} this is the same date column? or if it is different, clarify that it is a date column that you are pointing to and that you have set up the range in the current sheet and not just typed it in from another formula on a different sheet? (Each sheet need the cross sheet references re-defining - this would put up an unparseable error if the range had been copied from another sheet and not redefined)\\n=COUNTIFS({Portfolio Plan Range 3}, ISDATE(@cell), {Portfolio Plan Range 3}, AND(DAY(@cell) = 31, MONTH(@cell) = 12, YEAR(@cell) = 2020))\\nTry copying and pasting this one in to your sheet. (ensuring {Portfolio Plan Range 3} was defined and named on the current sheet.\\nPop a screen shot up on it in place if you are still getting unparseable.\\nGood luck\\nDebbie\\n\"}]","format":"rich","dateInserted":"2020-12-09T11:18:32+00:00","insertUser":{"userID":80301,"name":"Debbie Sawyer","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Debbie%20Sawyer","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/2019-02\/6f\/d1\/n6fd11e91b71000f2b152e84c8f072631.jpg","dateLastActive":"2023-08-06T09:27:03+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/271199#Comment_271199","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/271199#Comment_271199

Debbie, not exactly sure what changed but this did it! Thank you so much!!

Debbie Sawyer

Yay!

The only thing I could see was that Year was mixed case and not caps - but I can't imagine that was the error!

Glad it is working for you.

Kind regards

Debbie