If then else with DATE

Ask for Help
23

@Darren_Murphy @Milan_Balogh Geez, I had to put on my thinking cap before my morning coffee. The same place that Darren got that original formula had a second formula. It looks like that old thread is unlisted now, so I won’t link to it, but the second formula dealt with adding years, and also accounted for leap year, so if you added 1 year to Feb 29th, then the next year if would calculated to Feb 28th instead of March 1st. Essentially it would figure out if the month of the future calculated date is different from the month of the source date, and if it was, then calculate the difference in days using that difference in months (it would always be 1 or 0).

This doesn’t work well if that formula modified to add months instead of whole years. (Unfortunately, I’ve probably suggested a handful of times for others to use that other formula, which was wrong when calculating months instead of years). The problem is amplified when February is involved, since the month offset might be one, but we actually want to offset by 2 or 3 days to get the last day in February.

So I had to rethink it a little bit and fix my formula. One thing I did was add a TRUNC to some of the math because I noticed when doing (Month/12*365.25), it would result in a decimal value that would offset the time. This usually isn’t noticeable, especially if you are only showing the date without a time, but this could be a problem if the source date time is close enough to the beginning or end of the day, causing the time to float to a different day and throwing off all of the math. So now I’m truncating that decimal so we only get the true number of days when converting months to days.

I’ve included an updated formula at the bottom of this post, with a bit of a breakdown below. Hopefully I can explain it, so it somewhat understandable what’s happening.

  • First it takes the source date, subtracts the number of days based on the DAY in that date, then adds 15. We are just trying a get a date roughly in the middle of the month, so when you add any number of months (converted to days), we’ll still end up somewhere in the middle of the resulting month. When converting months to days, it’s really just an average number of days out of 365.25 which is 30.4375 days per month. That’s why I’ve chosen to use approximately the middle of the month (-days+15) when doing this math. The days may float a little bit, but not enough to put us in the wrong month.
    ((Date-DAY(Date)+15)+TRUNC(Months/12*365.25))
  • Next, we do that same formula again, but extract the resulting DAY (that’s somewhere in the middle of the resulting month). We take that result and subtract those days from the calculated future date. This actually gives us the last day of the month prior to the calculated date.
    (result of above) - DAY((Date-DAY(Date)+15)+TRUNC(Months/12*365.25))
  • Next we take the DAY number from the source date and add that to our calculated date. That way we end up with a future date that should have the same DAY number as the source date. These steps so far are the same as what @Darren_Murphy shared above, except I added the TRUNC.
    (result of above) + DAY(Date)

This gives us a date in the future that should have a matching day in most cases, but can be an issue if the source date has a DAY that does not exist in the month of the future date we are trying to calculate. For example…a source month having 31 days, but the resulting month only has 28, 29, or 30 days. The above formula would give us date that in the first 1, 2, or 3 days of the month AFTER the month we were trying to calculate. So because of that, we need to expand our math formula to now find the differential between the day of the date that was the result of our math, and the day of the source date. Then use that difference to subtract those days from the resulting date to get the last day of the month we actually wanted.

  • First we do the exact same math as above, but we only want to extract the DAY number from the resulting date. (Adding 1 month to Jan 31st gives us Mar 3rd, so we want to get the number ‘3’.
     DAY(((Date-DAY(Date)+15)+TRUNC(Months/12*365.25))
          -
          DAY((Date-DAY(Date)+15)+TRUNC(Months/12*365.25))
          +
          DAY(Date))
  • Using that resulting day number of the calculated date, we then subtract the DAY number of the source date. For example, adding 1 month to Jan 31st, gives you March 3rd, so what we are doing is taking 3-31 to get -28.
    (Result from original formula) - Day(Date)
  • The MOD of -28 divided by 31 days leaves a remainder of 3 days that we can then use to subtract from the resulting date to get the last day of the previous month (the month we were trying to calculate all along).
     (Result from original formula)
     -
     MOD((Result from above),DAY(Date))

TLDR…
I know…it’s confusing, but it’s all about breaking it down into pieces and understanding each piece. Here is the full formula that should work as expected. Be sure to test it thoroughly. I only threw a few dates at it, but I felt pretty good about the results. @Darren_Murphy, this may not be simpler, but it’s less columns. :beers: :stuck_out_tongue_winking_eye:

((Date-DAY(Date)+15)+TRUNC(Months/12*365.25))
-
DAY((Date-DAY(Date)+15)+TRUNC(Months/12*365.25))
+
DAY(Date)
-
MOD(
(DAY(((Date-DAY(Date)+15)+TRUNC(Months/12*365.25))
-
DAY((Date-DAY(Date)+15)+TRUNC(Months/12*365.25))
+
DAY(Date))-DAY(Date))
,DAY(Date))

EDIT: I will add @Darren_Murphy that this formula does not calculate to midnight, or change the resulting time to anything other that whatever time was in the source date, unlike what you showed in your post. I’m assuming that dates are being worked with as a whole, so the time portion of a date should be irrelevant.

9 Likes