Count Month from Date Value field

Sue Marmion

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!

Shaine Greenwood

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.

Sue Marmion

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?

 

Sue Marmion

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

 

 

 

J. Craig Williams

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

Rgioff

How would I go about counting a column of dates and counting each by month and year?