Find the last day of the following month

What is this wizardry. Hats off.

2 Likes

@Darren_Murphy Hehe, yeah, sometimes a person can end up in a weird place, like we did with the character counter a few weeks ago, but I agree, these kinds of challenges are fun and really do make you try to think out of the box. In my head, Iā€™ve already decided a solution is possible, so itā€™s hard to stop working on one once Iā€™ve started.

Ohā€¦I see COBOLGolf as an option. Now thereā€™s a true challenge to write short code in COBOL, but I donā€™t know if I want to subject myself to that kind of torture.

@Robert_Petitto if you get stuck, let me know.

2 Likes

Great @Jeff_Hager What a Solution!

1 Like

@Jeff_Hager how would you calculate the Ordinal Date using Glide math? (I know I could do it with my usual brute force approach, butā€¦)

Try this:

FLOOR(275 * MONTH(date)  / 9)
-
(FLOOR((MONTH(date) + 9) / 12)
*
(1 + FLOOR((YEAR(date) - 4 * FLOOR(YEAR(date) / 4) + 2) / 3)))
+
DAY(date)
-
30

Donā€™t ask how it works, because I donā€™t know. Found it here.
https://www.epochconverter.com/daynumbers

Also found some other formulas on other sites, but didnā€™t quite understand how to make it work in glide.

I thought I would maybe need a seed date column with a Jan 1st to calculate from, but this way works without a seed date.

5 Likes

Yes!!! Thank you!

This is what I needed it forā€¦

7 Likes

Nice! Thatā€™s pretty slick.

3 Likes

yeah, Iā€™m pretty happy with it.
The stacked bar charts are generated on the fly using quickchart.io
Itā€™s amazing how fast that is.

3 Likes

Yeah, Iā€™ve used quickcharts a few times. I like them a lot.

Just curious, would you have been able to accomplish the same thing in a more cumbersome way by incrementing days by adding or subtracting the increment value from a date, then converting dates to string dates without time using a template column, then using those dates for a relation? Doing it with ordinals is probably a lot easier, but Iā€™m just trying to picture a way in my head without ordinals, because I probably would have approached it differently. Using ordinals does give me some new ideas though. Especially by taking that ordinal and adding (year*1000) to get a value like 2021024 for todayā€™s ordinal number.

1 Like

You are a wizard :woman_mage: :star: :star: :star: :star: :star:

2 Likes

You beauty :+1:

1 Like

Mind blown :exploding_head:

1 Like

Yes, I tried a few different approaches before deciding that ordinals was the way to go.
I started off just trying to compare dates, but of course that didnā€™t work because NOW() is a constantly moving target. So then I started down the route of using templates to convert the date to a fixed string, but that got messy so I abandoned that idea. Then I decided that the best way to do direct comparisons was to first convert the dates into numbers. So I started with that by just doing Day(date), which worked but broke when I hit the month boundaries. Which finally led me to ordinals, and thatā€™s where you came in :slightly_smiling_face:

The ordinals will break at the year boundaries, butā€¦

You solved that problem for me as well :slightly_smiling_face:

2 Likes

May I ask for a little help pleaseā€¦ based on this solution Iā€™m trying to achieve
The same day one month later, and I used this (basically adding the days in my date to the end of this month:

((date-DAY(date)+15)+60)
-
DAY((date-DAY(date)+15)+60)
+
DAY(date)

And it kind of works except when my date is Jan 30 it will not respect February

Is there an easy way to simply get +1 month? Next week is super simple, but I couldnā€™t figure this out.

hehā€¦ I posted a video about 10 minutes ago that shows how to do this :slightly_smiling_face:

4 Likes

@Darren_Murphy @Jeff_Hager

I had missed this one. Wow, your solution Jeff is so elegant. I learnt so much from reading this thread. Thanks :pray:

1 Like

hehe, my 43 columns to Jeffā€™s one possibly not my finest hour :rofl:

Hereā€™s Jeff ā€œthe column monsterā€ dealing with all my superfluous columnsā€¦

3 Likes

Hey Jeff,

I am trying to figure out how to dynamically update a subscription period (No API).

The initial Start Date should be the date they sign up (that date is captured).
The initial End Date should be 30 days from the initial Start Date.

Next, I need the initial End Date +1 Day to become the new Start Date and find a new End Date 30 days from the new Start Date.

Ex:
Initial Start: 1/1/23
Initial End: 1/31/23
Next Start: 2/1/23
Next End: 3/2/23
Indefinitely

Is this possible? Also would be it be possible with not an excessive amount of Math Columns?

Thanks!

So to better understandā€¦youā€™re not asking for 4 columns, right? You only want a start date and an end date that both automatically update every 30 days?

It could be 4 columns, but I would only be comparing the current start and end date columns in another table to determine if a user has reached their limit.

I capture survey results and compare if the date the survey was taken was during the Userā€™s subscription period to know if it should be counted against their monthly limit.

I have both Free and Paid plans, where the Paid plans automatically have their subscription periods updated via API. However the Free Plan doesnā€™t get that luxury as all they need to do is create an account in my Glide App.