I have a list of items and would like to group them by week, based on a submitted date field.
Is there a formula I can use or display a week number for each? I would like each new week to start on Wednesday.
Thanks a lot!
use WEEKNUM formula
thank you so much! however, the weeks are slightly off since im trying to start new on Wednesdays. here’s the formula i use to accomplish this in airtable. any idea how to rebuild this logic in Glide?
ROUNDUP((DATETIME_FORMAT({Date},‘DDDD’)+3)/7,0)
In other words,
((Day of the year)+3)/7, rounded up
Where day of the year is a number from 1 to 365.
Thanks again. I really appreciate your help.
use the math column, and count how many days pass from the begging of the year - 3 / 7… now the question is… you will be calculating pass years or only the current year?
Try this in a math column and see if you get the results you are expecting.
CEILING((
(FLOOR(275 * MONTH(date) / 9)
- (FLOOR((MONTH(date) + 9) / 12)
* (1 + FLOOR((YEAR(date) - 4 * FLOOR(YEAR(date) / 4) + 2) / 3)))
+ DAY(date)
- 30)
+3)/7)
3 Likes
you should subtract 3, not add… Great formula @Jeff_Hager… but for the leap years, I would just create a 1/1/year date and subtract from DATE - 3 / 7
it works! thanks so much!
1 Like
Is there a list of all formulas somewhere? CEILING, FLOOR, WEEKNUM, DAY…etc? where can i find the full list with explanations. thanks again!
Most are listed in the docs. I think there are a few functions that aren’t listed (for example WEEKNUM is not listed), so it’s just trial and error or searching through the forum to see what’s possible and examples.
1 Like
I had a little more time to think about this and I’m trying to come up with a better formula. I based my previous formula purely on the formula you were using in airtable and didn’t really test it thoroughly, but I think there are some flaws with it.
I’m trying to understand one thing. Let’s take 2023 for example. January 1st begins on a Sunday. Would the 1st, 2nd, and 3rd (Sunday, Monday and Tuesday) be grouped together as week 1 and the 4th would begin week 2, or should they be grouped with week 53 from the prior year (included Dec 28th through the 31st), and Wednesday the 4th would actually be week 1. I guess my question is, do your week groupings only entail a single year, or can a week flow between years?
1 Like
Hey Jeff, thank you so much for continuing to help with this. The Week Groupings can flow between years. Ideally the 1st, 2nd and 3rd (Sun, Mon, Tues) would be grouped together as Week 1 and the 4th would begin week 2. But they could also be included in Week 53 from the prior year. It’s not important as long as long as each day is only included in one Week Grouping.