Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Count Month from Date Value field
Hello. I have setup a JIRA extract to provide raw data on which to develop an operational dashboard. I need to be able to count 'closed this month' on the dashboard. I am getting a closed date for each row from JIRA and, since this is an automated extract feeding a live dashboard, creating another column to extract the month from the date field for each row is not an option as it would require constant updating based on the growth of rows in the spreadsheet that will naturally occur as operational requests are pulled from JIRA through the connector.
I have tried to embed a formula to extract the Month value from the date field into the Countif formula, but am getting an #Unparseable error
=countif((month([Resolution Date]20),[Issue Type]3)
the value in the [Issue Type]3 field is a calculated variable that correctly resolves to current month. =MONTH([Resolution Date]3)
the value in [Resolution Date]3 is a calculated variable that correctly resolves to Monday's date for the current week. =TODAY() + 1 - WEEKDAY(TODAY() - 1).
I also use [Resolution Date]3 to count 'closed this week' and that formula is working correctly =COUNTIFS([Resolution Date]:[Resolution Date], >[Resolution Date]3)
These calculations are all in a 'header' area (as well as a number of other variable counts) so that these calculations will always be updated as the JIRA data gets updated automatically via the connector.
Can anyone help me with the correct formula to obtain a count of closed this month using a full date that resides in date field? format is mm/dd/yy
Thank you so much!
Comments
-
Hi Sue,
#UNPARSEABLE typically means that you have an extra paren in your formula, which in this case, it appears that you have an extra paren at the beginning.
Anytime a formula gives you a specific error code, check out the error article for a possible resolution: https://help.smartsheet.com/articles/2476176-formula-error-messages
I'm wondering if you're trying to get a count from the entire column into one field? If that's the case, you might want to do something like:
=COUNTIF([Resolution Date]:[Resolution Date], MONTH(TODAY()))
This will count any Resolution Date that has the current month.
I'm not sure that you need to count your Issue Type column, since you're using COUNTIF and not a function like SUMIF.
Let me know if I'm not on the right track with this.
-
Hey Shaine, thank you for the suggestion. I did detect that I was missing a parenth right after i posted this - so then the error moved to #InvalidDate.
I tried your suggestion and am no longer getting an error, however I am getting a result of zero and should be getting 21 - any ideas?
-
tried this
=COUNTIF(MONTH([Resolution Date]:[Resolution Date]), MONTH(TODAY()))
#Invalid Data Type
=COUNTIF(VALUE(MONTH([Resolution Date]:[Resolution Date])), VALUE(MONTH(TODAY())))
#Invalid Data Type
Tried formula in a text and a date field, same result
-
Try this instead
=COUNTIF([Resolution Date]:[Resolution Date], IFERROR(MONTH(@cell), 0) = MONTH(TODAY()))
The #Invalid Data Type is thrown because the range is a Date and MONTH(TODAY()) returns a Number.
Craig
-
How would I go about counting a column of dates and counting each by month and year?
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives