Find the last day of the following month

[Darren_Murphy]

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

My approach...

• 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 NOW is?
• 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 Day(T+CN), where 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 Day(T+62)

• 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 Month() function:

  • Figure out the month number for Today (Month(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: Month(CD1)

  • And then the final piece of the puzzle is a simple if-then-else:

       If CD1 Month = Next Month
        Then CD1
       Else
        CD2
    

Voila!