Have you ever found yourself wanting to use month names instead of month numbers in Google Sheets? It’s a common preference as month names are more reader-friendly. However, using month names as criteria in formulas involving date columns in Google Sheets can be a bit tricky. Don’t worry, though! In this article, we’ll explore a little tweak that allows you to use month names as criteria in the Query function in Google Sheets.
The Challenge with Using Month Names as Criteria in Date Columns
In Google Sheets, using month names as criteria may not work as desired in formulas when dealing with dates. Why is that? Well, month names are text strings, and comparing them directly with the values in a date column is not possible. So, how can we work around this limitation and use month names as criteria instead?
Converting Month Names to Month Numbers
The solution lies in converting the month name string to its equivalent number and using that number as the criterion. In a previous tutorial on Crawlan.com, we’ve explained how to convert a month name string to a month number in Google Sheets. For instance, if cell A1 contains the month name “January,” you can use the following formula to convert it to a number:
=month(A1&1)
But hold on! This method won’t work in the same way when using the Query function in Google Sheets. There are two crucial differences to consider:
- In Query, the count of month numbers is from 0 to 11, rather than 1 to 12.
- The
Month()scalar function in Query doesn’t work the same as the method mentioned earlier.
In the following examples, we’ll delve into how to use month names as criteria in date columns with the Query function in Google Sheets.
Method #1: Query by Month Name in a Date Column
This method is a more conventional approach but might appear a bit complicated at first. However, if you’re already familiar with using dates as criteria in the Query function, you’ll grasp it easily. If not, no worries! We recommend checking out our quick tutorial on how to use date criteria in Query function in Google Sheets. Once you’re acquainted with date criteria, you can proceed with the following formula:
=query(A1:B, "SELECT * WHERE month(A) = month(date '"&text(DATE(2019,month(D1&1),1),"YYYY-MM-DD")&"')", 1)
In this formula, we used the Month() scalar function twice. The first occurrence, at the beginning of the “Where” clause, tests the month in column A, which contains dates. Then, it matches the month number with the month number derived from the date in cell D1. Since the value in cell D1 is a text string, we utilized the Month() worksheet function to convert it to a month number as follows:
=Month(D1&1)
To ensure compatibility, we employed the Date function to convert the returned month number into a date that falls within the corresponding month. Finally, we used the Month() scalar function once again within the Query function to compare the month number with the month number in column A.
Method #2: Query by Month Name in a Date Column
If you prefer a simpler approach, this method is for you! Here, we only need to use the Month() scalar function once. Let’s take a look at the formula:
=query(A1:B, "SELECT * WHERE month(A)+1 ="&month(D1&1)&"", 1)
This formula may seem small, but it packs a punch. The first occurrence of the Month() scalar function directly checks the month number (plus one) in column A with the month number in cell D1. By adding one to the month number, we ensure compatibility between the 0-11 month numbers used in the Query Month() scalar function and the 1-12 month numbers returned by the Month() worksheet function.
Unlike the previous method, we don’t need to compare the month numbers returned by different scalar functions. Instead, we compare the output of the Query Month() scalar function with the output of the Month date function. While the Month date function returns 1 for January, the Query Month() scalar function returns 0 for January. As long as you pay attention to the quotation marks, this method is straightforward and efficient.
Using either of these methods, you can easily use month names as criteria in date columns within the Query function in Google Sheets. Now, you may be wondering if there are any additional resources related to this topic. Well, of course, there are! Check out the following:
- The Formula to Sort By Month Name in Google Sheets
- Convert Numbers to Month Name in Google Sheets
- Google Sheets Query: How to Convert Month in Number to Month Name in Text
- Populate a Full Month’s Dates Based on a Drop-down in Google Sheets
Now that you’re equipped with the knowledge of using month names as criteria in Query functions, go ahead and make your Google Sheets experience even more user-friendly and efficient! And for more exciting and informative content, head over to Crawlan.com.
