Find the last day of the following month

oh dear…

:rofl: :rofl: :rofl: :rofl:

3 Likes

2 Likes

It would be incredible to comment if I hadn’t run out of words :astonished:

3 Likes

Okay, so I’ve been playing around with that, and I can’t for the life of me figure out what’s special about the numbers 15 and 60??

1 Like

@Jeff_Hager

4 Likes

So, I’ll explain the logic a little bit. My main goal was to try to get the 1st day of the month, but two months from now. So for example, today is January 22nd. My goal was to figure out March 1st, and then simply subtract 1 day to get the last day of the previous month.

If you look at the formula, I am first taking today’s date, and subtracting the number of days from that same date. Technically that gives me December 31st as a result, but then I add 15 days. The reason I add 15 days is to get a day approximately in the middle of the current month. Now, no matter what today’s day is, we will always have a date around the 14th or 15th of the current month. Now we can add 60 days to get a date 2 months from now.

  • Using the +15 is a way to guarantee that we will always end up within the middle of a month when adding 60 days. Otherwise we have those edge cases where adding 60 days to the first or last day of a month may actually put you 3 months ahead. Example: adding 60 days to December 31st or January 31st would put you three months ahead because of February being a short month. If we can guarantee that the result is in the middle of the month, then we don’t have to worry about these edge cases.

Now that we have a date that’s in the middle of a month that’s 2 months from now, we take that same formula and do it again, but this time we wrap it in the DAYS function. With that we can take the current day from the date that’s 2 months from now and subtract that from itself, which will give us last day in the month prior to that.

  • So what we did is take January 22nd for example, then subtract 22 days to get December 31st.
  • Next we add 15 days to get January 15th as a result
  • Next we add 60 days to get March 16th as a result (technically could have just used 75 instead of 15 and 60, but 15 and 60 makes more sense visually)
  • Next we do that same formula, but wrap it in the DAY function so we get the value of 16 from the March 16th date.
  • Now we take March 16th minus 16 and that gives us February 28th as the final result.
14 Likes

I take my hat off to you, sir.
That’s such a great example of creative thinking.
Simply Superb

5 Likes

Simply superb

2 Likes

Thank you sir! That’s what I like to call, “thinking backwards”. Instead of thinking through steps to get to a goal, I like to figure out the last step first. In this case, I knew I could always subtract 1 from the first day of the month and always end up with the last day of the previous month (every month has the same first day, but never has the same last day). Then my next step was to figure out, how do I get the 1st of the month. I knew I could determine the day number in a given date and subtract that number from the same date. With that it was easy to determine the first day of a month from any given date. From there is was just working out the edge cases, and that’s where I came up with the +15.

5 Likes

Remarkable!

3 Likes

@Jeff_Hager you are a monster!
I thank you all for participating in the solution of this question. Thank you for taking all this time, I have to offer you all a beer!

3 Likes

Yes, I’ve seen you mention that thinking backwards approach before, and I actually had that in mind with my crack at it. In my case, my goal was ‘n’, because I knew once I had that I had the answer. So it was all about getting to ‘n’

Anyway, it was a fun and challenging exercise, and I bow to you once again :bowing_man:

PS. You should seriously consider taking up Perl Golf :wink:

1 Like

This was the key to unlocking this wizardry. Simply brilliant. …wondering if I can use something like this in another app I’m making for a client that wants recurring weekly and monthly tasks. I was struggling with the monthly tasks because each month has a different number of days and I needed to show “days until”.

3 Likes

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