oh dear…
oh dear…
It would be incredible to comment if I hadn’t run out of words
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??
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.
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.
I take my hat off to you, sir.
That’s such a great example of creative thinking.
Simply Superb
Simply superb
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.
Remarkable!
@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!
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
PS. You should seriously consider taking up Perl Golf
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”.
What is this wizardry. Hats off.
@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.
@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.
Yes!!! Thank you!
This is what I needed it for…
Nice! That’s pretty slick.