Asset Market
Ohh lovely…fun with figures
sorry lol
I learned more in a day reading posts on this forum than in 4 (wasted) years in college.
Thanks for the tip. God bless
Haha you’re excellent as well man, but Jesus is truly business savvy.
Figured out a way to get System/GMT time within Glide.
May have to follow the whole thread below, but I wanted to share something I learned today. If you create a template column with a date/time and any indication of a timezone, then create a math column to convert that template to a date, then it will lock that date/time to that timezone, but show the date/time converted as it would be in the user’s local timezone. It has some weird issues sometimes and doesn’t seem to allow for all types of date math, but it could solve a lot of people’s issues with locking a date/time to a specific timezone.
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.
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!
Glad it could help! I was thinking this morning “Who the hell would know which range covers Week 23 - 2021?” haha.
Right?!
@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
Yesss very happy to see it would be benificial for my fellow devs on here
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?
Will be my morning brain exercise tomorrow!
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.
& our Jean-Claude Van Damme attack again!
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
Perfect, thank you!!