Hey Gliders,
I have a google sheet with a list of crops. Each crop has two date columns - Ready for Harvest and End of Harvest.
I have another glide table called “What’s available” with a column of all Mondays from June through August. So for e.g.
row 1 - June 5, 2023
row 2 - June 12, 2023
and so on…
I want the ‘Whats Available’ glide table to pull all the crops that would be available for harvesting as long as the Monday (e.g. June 5, 2023) falls between the two dates in the crop sheet - Ready to Harvest & End of Harvest.
The way I was hoping to accomplish this was to see if I could get a date array of all Mondays between the ‘Ready to Harvest’ and ‘End of Harvest’ in the crops sheet. Then I would set a date relation between the ‘What’s Available’ table and Crops sheet and then use lookup to get all crops names where the ‘What’s Available’ date falls between the ‘Ready to Harvest’ and ‘End Harvest’. Is there a way to get a date array of all Mondays between two dates? Or perhaps there is a more cleaner way to accomplish this.
Also, I am using classic apps and don’t know how to write scripts.
Enabling Query preview and using it in the Harvest Calendar sheet worked for a split second. I could see the results and then it wiped off. Sharing screenshots. Is it a glitch in query or is there something I am doing wrong.
I’m not sure, this is typically the type of situation where I would need to tweak with a real example and see the output, but try changing the configuration of your Query Column:
Ready For Harvest is on or beforeWeek
And
End of Harvest is on or afterWeek
it is working now. I just tried a few random things like deleting the glide table and starting over. and then playing with things like ‘before’, ‘on or before’ etc. Those didn’t work. What worked is setting a relation column between the Harvest Calendar (Glide Table) and Crop sheet. and then using the query.
I am not smart enough to figure out why this worked. So just passing this info along so others can make sense of it.
Thanks to all of you who chimed in on this.