Calculate number of rows with current month & year in Google Sheets or Excel, when dates are in ISO format

0
votes

I have one column filled with dates in ISO format (with header row), such as:

DATES
2015-05-13 12:46:41
2015-03-19 11:50:13

I would like to count the number of rows which match the current month & year. In the example above, the result would obviously be one (when it's May 2015).

Is this possible to achieve this result using a function in Google Spreadsheet?

Same question without ISO date can be found at:

Calculate number of rows with current month & year in Google Sheets or Excel

1
excelgoogle-sheetsexcel-formula
I assume you tried those solutions but they didn't work? If the values are true date/time values the format (i.e. the way it's displayed) shouldn't stop those formulas from working - if they don't work that probably means you have text values, not dates - try converting by doing this: Select Date Column > Data > Text to columns > Finish – barry houdini
These dates are actually pushed to the spreadsheet by Zapier. Whenever a new date is pushed via the API I would like the counter to be increased. It seems that they indeed are text, but I couldn't find a way to automatically treat them as dates. – tputkonen
OK, I posted an answer with a formula you can use on text formatted dates – barry houdini

1 Answers

1
votes

If those are text values, as per my comment, then the suggested formulas in the link won't work - either convert as suggested or you can use this formula to count text values that represent the current month

=COUNTIF(A:A,TEXT(TODAY(),"yyyy-mm-")&"*")

or avoiding date formats in text function

=COUNTIF(A:A,YEAR(TODAY())&"-"&TEXT(MONTH(TODAY()),"00")&"-*")

change commas to semi-colons depending on regional settings