Mondays between two dates

Ask for Help
1

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.

Thanks in advance!

2

Glide is releasing the Query column later this week. I’m not sure, but I think it could help address your request nicely.

3

Woah! This must be how it feels to get a wish granted by the geni(us) at Glide! :pray:

4

It is a simple JavaScript… i can write it for you

5

It’s in production already, you just need to enable it in Previews.

@Food2Soil curious how this turns out for you. If you can share some screenshots of sample data, we can brainstorm together!

1 Like
6

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.

query
query1530×725 59.5 KB

1 Like
7

That’s weird. Can you refresh the tab and try again? I think you’re quite close to your solution.

1 Like
8

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 before Week
And
End of Harvest is on or after Week

9

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.

query1
query11530×768 65.4 KB

query2
query21530×769 62.2 KB

query3
query31530×773 76.5 KB

2 Likes
10

A Query on the Relation?

Either way, congrats for making it work!