I’m trying to create a query formula that calculates years based on a condition but I can’t get it working properly. Here’s what I’m attempting:
=query('Survey Data'!A:O,"Select if(month(H)>8,year(H)+1,year(H)) , O,A,B,C,D,H,I,J,K,L,M where H >= date '2012-09-01' and H <= date '2049-08-31' order by H asc, A")
What I want is to show a modified year value in column H depending on whether the month is greater than 8. If it is, I want to add 1 to the current year, otherwise just show the regular year.
The query runs but doesn’t give me the expected results. Can someone point out what I’m doing wrong with this conditional projection? I’ve been stuck on this for hours and would really appreciate some guidance.
Hit this exact problem last month with academic year data. You’re right that CASE won’t work - Google Sheets query function barely supports SQL compared to real databases. I scrapped trying to calculate the conditional year in SELECT and went with a nested approach instead. First filter and sort your data, then apply the year calculation using regular spreadsheet functions on the query result. Wrap your basic query with IF(MONTH(query_result)>8,YEAR(query_result)+1,YEAR(query_result)). More setup but it sidesteps the syntax limits and keeps everything in one formula without helper columns cluttering your sheet.
The issue you’re facing with the Google Sheets Query function stems from the inability to use IF() directly in the SELECT clause. A reliable workaround is to create a helper column within your data set. In this column, you can use the formula =IF(MONTH(H2)>8,YEAR(H2)+1,YEAR(H2)). This will calculate the modified year based on your condition. Then, simply reference that helper column in your query instead of incorporating the logic within it. I’ve encountered a similar problem in the past, and using helper columns has consistently resolved it.
Google Sheets query limitations are annoying when you need complex calculations. Helper columns work but they’re a pain to maintain.
I’ve hit similar walls with data transformations in production. The real fix? Move this to a proper automation platform that handles conditional calculations without breaking a sweat.
Stop fighting Google Sheets and set up a workflow that processes your survey data automatically. Connect to your sheets, run the conditional year logic with actual programming, and write results back. No more helper columns cluttering your data.
This saves hours of formula debugging and actually scales. You can add more transformations later without hitting spreadsheet walls.
Check out Latenode for this: https://latenode.com
yea, that’s a classic google sheets limitation - the query syntax is pretty basic compared to sql. quick hack: try using case instead of if in your select clause like case when month(H)>8 then year(H)+1 else year(H) end. not 100% sure if sheets supports it but worth a shot before you resort to helper columns.
Query function doesn’t support IF statements in the SELECT clause like that. Google Sheets query has pretty limited support for complex functions, so what you’re trying won’t work. I hit this same issue building financial reports with fiscal year calculations. Easiest fix I found: use ARRAYFORMULA with your conditional logic outside the query. Create your modified year calculation separately with =ARRAYFORMULA(IF(MONTH(H:H)>8,YEAR(H:H)+1,YEAR(H:H))) then query that result. Or just pull the raw data first, then apply your conditional year logic after. Way easier to control and debug when stuff breaks.