Heyy!! so usually I just love the logical challenges that glide gives me but this one has got me in the mud… I want to build an employee scheduling app that has groups of weeks (let’s say a group from 2-8.4,9-15.4, etc…) that updates automatically in the Callander. for that I need to somehow find the start of every week in a calendar month and a way to update the list automatically with 2-3 weeks ahead. any help would be greatly appreciated! thanks in advance!
Hello and welcome to the community! 
With the use of a helper table we could calculate the start date of coming weeks. Use this formula in a math column where ‘Now’ is replaced with the Now value and ‘IDX’ is replaced with a hard coded column.
Now-Hour(Now)/24-Minute(Now)/24/60-Second(Now)/24/60/60-Weekday(Now)+1+IDX
Another approach could be to use a one row table and swap out the IDX column to hold a user specific value tied to a choice component.
In the third column I converted our dates to a number to depict the week# i.e. 15, 16, 17, etc… In a new Math column use the formula below and replace ‘Date’ with the output from the previous step. When working with Relations+Dates it’s good practice to convert to a number first.
year(Date)*10000+Weeknum(Date)*100
I’m not sure if you’ll need this last part because I don’t know what your app looks like but hopefully this gives you some ideas.
FYI Glide treats Sunday as the first day of the week.
Edit: Here’s one way to count a particular weekday for the current or future month.
2 Likes
first of all, thank you so much for the response! its bloody genius! but I do think that you misunderstood me… I needed to find the first day of the week from a known date (like lets say the date is 4.4.23 then the first day of the week will be Sunday at 2.4.23) I probably needed to be more specific with my question… also, what is IDX? (I’m sorry math has always been a subject I’ve struggled with so I didn’t 100% understood the explanation… ) again, tysm for your response and for your help!!
To simply get the first day of the week from a given date, the it should just be a matter of subtracting the weekday from the date. So something like this should work.
Date-WEEKDAY(Date)+1
There are some other considerations regarding the underlying time, and I thought @Eric_Penn was trying to address that as well, but I guess it all depends on what you ultimately do with that calculated date. Just something to keep in mind, that every date column still has an underlying time value, even if you formatted it to only show date. One trick to deal with that is to wrap your formula with a TRUNC. That will trim the time and give you a google style numeric representation of the date, which could be useful for relations, grouping, etc. It would be something like this.
TRUNC(Date-WEEKDAY(Date)+1)
4 Likes
Thank you so much!! @Eric_Penn 's answer was really good but I didn’t understood it because of math stuff and all… I needed a really straightforward answer cuz I just get lost easily you know?..
1 Like
This topic was automatically closed 24 hours after the last reply. New replies are no longer allowed.