Fun with Dates

Asset Market :shushing_face:

image

5 Likes

Ohh lovely…fun with figures

1 Like

:clown_face: 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 :trophy: :1st_place_medal: :moneybag:

7 Likes

Haha you’re excellent as well man, but Jesus is truly business savvy.

Figured out a way to get System/GMT time within Glide.

2 Likes

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.

3 Likes

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.

Right?!

1 Like

@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 :pray:

3 Likes

Yesss very happy to see it would be benificial for my fellow devs on here :grinning_face_with_smiling_eyes::grinning_face_with_smiling_eyes::grinning_face_with_smiling_eyes:

2 Likes

Actually, I spoke too soon :crazy_face: :rofl:

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? :thinking:

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

Perfect, thank you!!

2 Likes