3

I'm trying to use DCOUNTA() to count a range, the problem is that within the range there are date and times and I only want to match the months.

This function doesn't seem to work: DCOUNTA('QA Productivity Worksheet'!A2:F, "Colleague", {{"Colleague"; $A5}, { MONTH("Date"); MONTH($A$1) }})

I have also tried: TEXT("Date", "MM") with no success.

I'm pretty sure I'm using the wrong syntax to extract just the month from the "Date" column. How would I go about this?

  • 1
    "Criteria_arr is a range, embedded array or array generated by an array expression that is structured such that the first row contains the field name(s) " -- documentation. So it doesn't look like you can apply any scalar functions to the field prior to testing it against the criterion. I suggest using query instead, which allows this. – user79865 Oct 28 '15 at 6:58
1

You need to use regexreplace first to remove the time part of the time stamp, and then the Text() function will properly transform the way you want.

=TEXT(REGEXREPLACE("date timestamp string","\d+:\d+:\d+",""),"MM")

Note: the timestamp part of the formula - may need to be altered depending on what exactly your string looks like, if you post an example of the string your starting with - I can modify the regex for you to fit that criteria

ALso if you share an example sheet - we can incorporate your DCOUNTA or a COUNTIF alternative

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.