I was wondering if it is possible, directly in Glide (without using sheet), to calculate the last calendar day of the month following the current one.
For example: today is January 22, the date I need is February 28, 2021.
This is useful for some payment installments in commercial contracts.
ooooh, I can’t wait to see what @Jeff_Hager comes up with for this one
I’ve figured out a brute force approach that I believe will work. I’ll post it once I’ve had a chance to test and validate it.
Okay, I have a “solution”. And it ONLY requires 40 math columns and 3 if-then-else columns
So no, please don’t take this as a serious suggestion. I just enjoy a challenge
- The first thing that occurred to me when I started thinking about this is that if we know (or can discover) how many days there are between “today” and the last day of next month, then the answer is simple. It’s just
NOW + n, where
n is the number of days.
- So how to figure out what
n is, when you don’t know in advance what
- One thing I realised pretty quickly is that
n is a finite set of sequential integers, where:
- the lower bounds is the number of days between Jan 31 and Feb 28 (28, in a non-leap year)
- the upper bounds is the number of days between Jul 1 and Aug 31 (61)
- Okay. So that means that
n will always be an integer between 28 and 61 for any given date.
And this is what led me to realise that it should be possible to find the answer using a brute force approach. All we need to do is figure out a way to test for all possible values of
n, and we should eventually arrive at the correct answer.
However, there is a slight snag. And that comes with the fact that the count of all possible values of
n (34) is greater than the minimum number of days in a month (28). And what this means is that for some dates, there could be two possible answers. For example:
- Apr 1 + 28 = Apr 29 (wrong)
- Apr 1 + 60 = May 31 (correct)
Both 28 and 60 are in the set of possible
n values, so how to decide which is correct?
Step by step...
I started off by creating 34 Math columns, labelled T28 to T62. (actually I didn’t really - I just created T28 to T35, and T55 to T62 - but that was enough to prove that it works)
Each of those math columns uses the formula
T is Today, and
CN is the column number. So column T28 has
Day(T+28), column 29 has
Day(T+29), etc, etc… all the way up to column T62, which is
What that gives us is the day of the month of today plus the number of days represented by that column number
Here is what that looks like in the Data Editor:
So now we essentially have all possible values of
n for each of the values of Today in that table.
The next step is to figure out which is the correct one. What we are looking for is:
- The highest number in each row, BUT
- It must be in the next month
Okay, so the next thing I did was to create two if-then-else columns, DD1 & DD2 (DD for Days Difference):
- The first one (DD1), starts at the bottom and works its way up, comparing the value of each column to the value of the preceding column. If it’s decreased, that means we’ve hit the end of the month, and so we use the number of the previous column. Here is what it looks like:
This gives us our first “candidate” value for
n. We don’t know if it’s correct yet because it might not be in the next month.
To get the second candidate, DD2 is used. It does the same thing as DD1, except in reverse - working from the top down.
With 2 candidate values for
n, we can now calculate two potential answers with two simple math columns -
Today + DD1 and
Today + DD2, which become CD1 and CD2 respectively
The final piece in the puzzle is to figure out which candidate is correct. For this I used the math
Figure out the month number for Today (
This gives us a number, so all we need to do is add one to that and we know what the month number of next month should be.
CD1 and CD2 are both dates, so let’s grab the month number of CD1:
And then the final piece of the puzzle is a simple if-then-else:
If CD1 Month = Next Month
@Darren_Murphy how about 1 column? Hold my
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.
- 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.
I take my hat off to you, sir.
That’s such a great example of creative thinking.
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.
@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”.