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?

Improve this question
1
  • 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
    Commented Oct 28, 2015 at 6:58

1 Answer 1

Reset to default
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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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