Home > front end >  Replacing the Month Number with the Month Name from a Query()
Replacing the Month Number with the Month Name from a Query()

Time:05-09

I am grouping data by month in a Google Sheets QUERY using the MONTH function. However, it returns the month number from 0-11 and I prefer to see the month names.

Here is the QUERY

=QUERY(DataAllMonths!A1:B, "SELECT MONTH(A), COUNT(A) WHERE LOWER(B) LIKE '%no empathy%' GROUP BY MONTH(A) LABEL MONTH(A) 'Month', COUNT(A) 'Total'", True) 

This returns something like the following:

enter image description here

I ended up writing a customer function to take the results from the QUERY and substitute the month names for the numbers

=returnMonthName(QUERY(DataAllMonths!A1:B, "SELECT MONTH(A), COUNT(A) WHERE LOWER(B) LIKE '%no empathy%' GROUP BY MONTH(A) LABEL MONTH(A) '', COUNT(A) ''", True), 1)

And this shows the following result

enter image description here

Here is the script

function returnMonthName(data, col) {
  // var data = [[1, 100], [2, 200], [3, 300]];
  // var col = 1;
  var months = [ "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec" ];
  var retArray = [["Month","Total"]];
  var total = 0;
  // var retArray = [];
  var n = 0;
  var dataLen = data.length;
  for (n = 0; n < dataLen; n  ) {
    retArray.push([months[data[n][0]], data[n][1]]);
    total = data[n][1];
  }
  retArray.push(["Total", total]);

  return retArray;
}

I believe there is a way to do this without depending on a custom function, perhaps using an array formula. Does anybody have a solution.

enter image description here

and change format of column to MMM

enter image description here

reference

EOMONTH

  • Related