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:
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
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.
and change format of column to MMM