Find the last day of the following month

Ask for Help
13

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.
18 Likes