Yes this is correct… this would be the ideal result yes
Okay, so an easy way to do this would be to combine both approaches - that is, use the math formula to add 1 month in one column, and use the formula to calculate the last day of the following month in a second column. And then use a bit of if-then-else logic to decide which is “correct” by comparing the month numbers of each.
So something like this:
I suspect that @Jeff_Hager might be able to come up with a simpler approach 
2 Likes
@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. 
((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
Jeff You Are Super Cool! 
I imagine that if the Hyper-formula column is available this could be easy!
=EDATE(A5,1)
I agree! It’s a lot of work just to add a month, while keeping the day number consistent, or handling the edge cases with shorter months. From what I understand, typescript/javascript doesn’t have a native function to add a duration in months, so that might be why glide really doesn’t have a native function either. I think a lot of glide’s functionality is based on what’s available in javascript.
I suppose the end result can be interpreted different ways. Maybe adding a month could be interpreted as finding the same day number one month from now, or finding the same day of the week one month from now, or using an average number of days (30.4375) from now, or any other variation of interpreting what is considered a month.
What would be awesome is if we had an AddDur function, with 3 parameters. First the date, second the units (year/month/day/hour/minute/second), and third the number of units. Maybe there would need to be a fourth optional parameter to set the different types of ways to interpret a month. Something like this:
addDur(Date, 'M', 1, x)
Just thinking out loud…
6 Likes
mmm, I have a habit of doing that when I’m working with dates and I only care about the date part. It’s usually not necessary because I’ll wind up converting the date to a string or an integer before using it elsewhere. But I’ve just been tripped up so many times by that dangling time - and Glide makes that super easy because it’s often hidden from view - so it’s a bit of an extra security blanket
1 Like
Yeah I guess it depends. I know IF conditions can be tricky with dates, since some of them only use the date and some include time. So I suppose it depends on which kinds of IF conditions you use.
1 Like
Thank you @Jeff_Hager , this works amazingly well, now I tested it in my application. And really appreciate the explanation too!
1 Like
I used this approach because everything else was too complicated with formulas etc. Long formulas unnerve me and since I just needed to compare two dates this method seemed simpler. Thanks for the great discussion on this.
1 Like