# Fun with Dates **URL:** \ **Category:** Project Showcase\ **Created:** [January 24, 2021, 8:25pm UTC](https://community.glideapps.com/t/fun-with-dates/21684 "2021-01-24T20:25:08Z")\ **Posts on this page:** 20\ **Page:** 7
**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\ **Post date:** [June 3, 2021, 3:44am UTC](https://community.glideapps.com/t/fun-with-dates/21684/123 "2021-06-03T03:44:47Z")
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). ![image](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/5/6/564291925efd62b11c186dbbe0555c1afcb254ff.png) Start date: Idea was moving everything to the current Sunday, then move back 6 days. ![image](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/7/6/76911e7f402a05f3e0c476c4245fbca6d0c39c46.png) 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. ---
**Author:** ![Robert\_Petitto](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/robert_petitto/32/25193_2.png) [@Robert\_Petitto](https://community.glideapps.com/u/Robert_Petitto)\ **Post date:** [June 3, 2021, 5:33am UTC](https://community.glideapps.com/t/fun-with-dates/21684/124 "2021-06-03T05:33:10Z")
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! ---
**Author:** ![Robert\_Petitto](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/robert_petitto/32/25193_2.png) [@Robert\_Petitto](https://community.glideapps.com/u/Robert_Petitto)\ **Post date:** [June 3, 2021, 5:57am UTC](https://community.glideapps.com/t/fun-with-dates/21684/125 "2021-06-03T05:57:15Z")
Looks so much better. Thanks @ThinhDinh! ![Screen Shot 2021-06-03 at 1.56.51 AM](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/8/d/8d7f94235ab9d483e80b5bf9461023037dd3ce15.png) ---
**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\ **Post date:** [June 3, 2021, 7:18am UTC](https://community.glideapps.com/t/fun-with-dates/21684/126 "2021-06-03T07:18:07Z")
Glad it could help! I was thinking this morning “Who the hell would know which range covers Week 23 - 2021?” haha. ---
**Author:** ![Robert\_Petitto](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/robert_petitto/32/25193_2.png) [@Robert\_Petitto](https://community.glideapps.com/u/Robert_Petitto)\ **Post date:** [June 3, 2021, 7:41am UTC](https://community.glideapps.com/t/fun-with-dates/21684/127 "2021-06-03T07:41:47Z")
Right?! ---
**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\ **Post date:** [June 11, 2021, 9:29am UTC](https://community.glideapps.com/t/fun-with-dates/21684/128 "2021-06-11T09:29:45Z")
@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` ![Screen Shot 2021-06-11 at 5.27.33 PM](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/c/d/cd3d88b38440796c84233fc1beed6f42b0d3457d.png) Thank you, little brother 🙏 ---
**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\ **Post date:** [June 11, 2021, 10:24am UTC](https://community.glideapps.com/t/fun-with-dates/21684/129 "2021-06-11T10:24:41Z")
Yesss very happy to see it would be benificial for my fellow devs on here 😄😄😄 ---
**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\ **Post date:** [June 11, 2021, 11:22am UTC](https://community.glideapps.com/t/fun-with-dates/21684/130 "2021-06-11T11:22:12Z")
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? 🤔 ---
**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\ **Post date:** [June 11, 2021, 2:57pm UTC](https://community.glideapps.com/t/fun-with-dates/21684/131 "2021-06-11T14:57:17Z")
Will be my morning brain exercise tomorrow! ---
**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\ **Post date:** [June 11, 2021, 11:42pm UTC](https://community.glideapps.com/t/fun-with-dates/21684/132 "2021-06-11T23:42:21Z")
I hope this is right. ![image](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/7/8/785a9697f334badaccc4ce351a26dbab4fc6122c.png) ```auto 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. ![image](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/6/2/622f7ab554bc9c3390e1019dd6aea6799a7bf7ae.png) ```auto 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. ---
**Author:** ![Lucas\_Pires](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/lucas_pires/32/64863_2.png) [@Lucas\_Pires](https://community.glideapps.com/u/Lucas_Pires)\ **Post date:** [June 11, 2021, 11:46pm UTC](https://community.glideapps.com/t/fun-with-dates/21684/133 "2021-06-11T23:46:23Z")
& our Jean-Claude Van Damme attack again! ---
**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\ **Post date:** [June 11, 2021, 11:55pm UTC](https://community.glideapps.com/t/fun-with-dates/21684/134 "2021-06-11T23:55:06Z")
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. ![image](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/d/f/dff68e4bf860e31449720196530389bcb2c8cd9f.png) And here we go! Tested some timestamps. ```auto 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. ```auto 31-(FLOOR(DAY(N)/16))*15 ``` ---
**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\ **Post date:** [June 12, 2021, 12:36am UTC](https://community.glideapps.com/t/fun-with-dates/21684/135 "2021-06-12T00:36:27Z")
Perfect, thank you!! ---
**Author:** ![Lucas\_Pires](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/lucas_pires/32/64863_2.png) [@Lucas\_Pires](https://community.glideapps.com/u/Lucas_Pires)\ **Post date:** [June 12, 2021, 1:03am UTC](https://community.glideapps.com/t/fun-with-dates/21684/136 "2021-06-12T01:03:12Z")
> [@ThinhDinh](#): > > Darren’s idea made me think about a formula to derive the current month’s number of days. oh gosh 🤯 ---
**Author:** ![Lucas\_Pires](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/lucas_pires/32/64863_2.png) [@Lucas\_Pires](https://community.glideapps.com/u/Lucas_Pires)\ **Post date:** [June 12, 2021, 1:07am UTC](https://community.glideapps.com/t/fun-with-dates/21684/137 "2021-06-12T01:07:56Z")
Incredible to participate in a community with [Jean-Claude Van Damme](https://community.glideapps.com/u/thinhdinh), [Sylvester Stallone](https://community.glideapps.com/u/Darren_Murphy) & [Chuck Norris](https://community.glideapps.com/u/Jeff_Hager) being part of us ---
**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\ **Post date:** [June 12, 2021, 1:22am UTC](https://community.glideapps.com/t/fun-with-dates/21684/138 "2021-06-12T01:22:10Z")
![](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/4/1/41c4bcab4ace66cc03cb9438a483cce613b402a0.gif) 🤣 ---
**Author:** ![Robert\_Petitto](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/robert_petitto/32/25193_2.png) [@Robert\_Petitto](https://community.glideapps.com/u/Robert_Petitto)\ **Post date:** [June 12, 2021, 1:37am UTC](https://community.glideapps.com/t/fun-with-dates/21684/139 "2021-06-12T01:37:17Z")
Omg that’s fantastic. ---
**Author:** ![Lucas\_Pires](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/lucas_pires/32/64863_2.png) [@Lucas\_Pires](https://community.glideapps.com/u/Lucas_Pires)\ **Post date:** [June 12, 2021, 1:38am UTC](https://community.glideapps.com/t/fun-with-dates/21684/140 "2021-06-12T01:38:46Z")
![](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/6/0/60e89f80112b1a33efc0141853ff90a168feaf21.gif) ---
**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\ **Post date:** [June 12, 2021, 1:56am UTC](https://community.glideapps.com/t/fun-with-dates/21684/141 "2021-06-12T01:56:11Z")
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. ---
**Author:** ![Robert\_Petitto](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/robert_petitto/32/25193_2.png) [@Robert\_Petitto](https://community.glideapps.com/u/Robert_Petitto)\ **Post date:** [June 12, 2021, 3:39am UTC](https://community.glideapps.com/t/fun-with-dates/21684/142 "2021-06-12T03:39:34Z")
For sure, though, technically, the next billing date is one of the API parameters. [Previous page](https://community.glideapps.com/t/fun-with-dates/21684.md?page=6) [Next page](https://community.glideapps.com/t/fun-with-dates/21684.md?page=8)