I hope people would find this useful, I had a use case that needs users to submit a form once per week.
Showing the week number to the admin might not be the most informative, so I used these to derive the start date and end date of each week (Monday and Sunday).
Start date: Idea was moving everything to the current Sunday, then move back 6 days.
End date: Same as above, but just move everything to the currrent Sunday.
Weekday column was a simple weekday formula in a math column, taking from the timestamp.
Had to go for this since the default weekday start is Sunday (numbered 1).
Edited to fix an error in the start date.
12 Likes
Nice! Was wondering if there was an elegant way of displaying the week dates without the need to list them all out in a sheet somewhere.
You cracked it!
1 Like
Looks so much better. Thanks @ThinhDinh!
1 Like
Glad it could help! I was thinking this morning “Who the hell would know which range covers Week 23 - 2021?” haha.
@ThinhDinh I just knew would need this one day… just had a use case where I need the date of the first day of the current week (starting on Monday)
Now+Mod(8-Weekday(Now),7)-6
Thank you, little brother 
3 Likes
Actually, I spoke too soon 
What I realised is that the above also gives me the current time on the first day of the current week. But what I actually need is midnight on the first day of the current week. ie. “midnight last Monday”.
How would you adjust it to get that?
And for extra brownie points, how would I get midnight on the first day of the current month? 
2 Likes
Will be my morning brain exercise tomorrow!
1 Like
I hope this is right.
N+MOD(8-WEEKDAY(N),7)-6-HOUR(N)/24-MINUTE(N)/1440-SECOND(N)/86400
Gets the now of last Monday, then subtract all the remaining hours, minutes and seconds.
N+1-DAY(N)-HOUR(N)/24-MINUTE(N)/1440-SECOND(N)/86400
Subtract today by the “date” of today then add 1, then subtract all the remaining hours, minutes and seconds.
4 Likes
& our Jean-Claude Van Damme attack again!
4 Likes
Jean-Claude does not stop here hehe.
Darren’s idea made me think about a formula to derive the current month’s number of days.
And here we go! Tested some timestamps.
N+31-(FLOOR(DAY(N)/16))*15+1-DAY(N+31-(FLOOR(DAY(N)/16))*15)
-
(N+1-DAY(N))
Edit: Realized a bug with 28 is that when you start with dates like 1, 2 in a 31-day month then it does not convert. Back to the drawing board.
Edit 2: Derived a way to add 16 days when it’s “day 16 or more”, and 31 days when it’s “less than day 16” to make sure it goes to the next month.
31-(FLOOR(DAY(N)/16))*15
8 Likes
Incredible to participate in a community with Jean-Claude Van Damme, Sylvester Stallone & Chuck Norris being part of us
5 Likes
I imagine it would be useful to calculate the next billing date in a monthly subscription. With PayHere, up until now, I do it in the Sheet.
For sure, though, technically, the next billing date is one of the API parameters.
2 Likes
