You can use MONTH function in Google Sheets Query to return month number from Date. Compare to the Month Function which is part of Date Functions, the Month Scalar Function behaves differently in Query. I mean, for January it would return 0 and for February 1. But you can change this with a simple trick. So in this tutorial we can learn the use of Month Function in Query. It would be useful for you to create month wise Summary in Query.
In an earlier tutorial, I’ve detailed how to use Google Sheets Query Formula to create a Month Wise Summary. But there, instead of using Month function directly within Query, I applied a different approach using ArrayFormula. You can find link to that tutorial below.
So here we go.
Use of Month Function in Google Sheets Query
Here you are not only going to learn how to convert date to month in Query, but also to summarise data based on month.
Here what I am going to do is, summarising the products month wise. That means I’m going to find the total sales amount of each products in different months. The summary would be as follows.
There are two steps involved in the above procedure. We are starting the tutorial here.
How to Use Month Function With Query in Google Sheets
We are making use of only three columns in the above sample data.
1. Column A which contain date.
2. Column B which contain Product.
3. Column F which contain the amount of product.
Here in the Query formula, I’m going to convert the date in Column A to month. Then grouping this month and Column B product to find the sum in column F. Below is that formula.
=query(A1:F7,”Select Month(A)+1,B, Sum (F) group by Month(A)+1,B”,1)
Here column A contain date, and normally a formula like Month(A) would return month number. Then why the +1?
This is simply because, in Query, the months are 0-based. Needless to say the function would then return 0 for January, 1 for February, etc. By adding 1 to the returned value, we can get the usual month number for our purpose.
See the result of the above formula.
You can consider this as a new data range. So we can use an additional Query formula to pivot the above summary. I’ve used the above formula as a new range in the second Query formula below. The coloured part is the first Query Formula as range in the second formula.
=query((query(A1:F7,”Select Month(A)+1,B, Sum (F) group by Month(A)+1,B”,1)),”Select Col1, Sum (Col3) group by Col1 Pivot Col2 label Col1’Month'”)
This time we can’t use Select A, B like wise in Query as we are dealing with a virtual range. So Query supports Col1, Col2 instead. I’ve followed the same in the above formula. I’ve selected Col1, then sum Col3 and used Col2 to Pivot. Also, a label applied to clean the column label.
See the above screenshot 2 for the output. This way you can use Month Function in Google Sheets Query and also make month wise summary.