Is rolling up summary values based on dates - is there an easier way to do this?

[KPT]

I have an app that needs to roll up different values for a dashboard section.
Fx how much money each individual has logged in the past 3 months:

This month $ A
Last month $ B
Two months ago $ C
Three months ago $ D

I have this month figured out by having a math column calculating todays Year and today month with the following:

Year(Now) * 100 + Month(Now)

and then another column that figures out the date and month for the logged date for a given record

Year(Date) * 100 + Month(Date)

Then an IF/Else Column to see when the two field align which is then rolled up in my users tab.

I can easily do the same for the three other months, by just subtracting 1-3 months from Month(Now).

My problem is when it’s comes to Jan, Feb and March and I need to also change the year and add to the month rather than substracting?

I can come up with a solution that requires a lot of math columns and IF/Else columns that checks if the Todays year and month is smaller than the logged date and then substract 1 from the year and add to the month accordingly.

But before embarking on that journey I wonder if there is an easier way around this?

[Jeff_Hager]

Can we add add a entire year into a trigger action

I actually have an updated version of that formula, which I think takes care of a couple of odd issues when adding months to a date that results in the end of February. Probably doesn’t matter for your use case of adding a full year, but if you ever use it for adding months, then I would recommend the new version instead. (I should update my original post) You could also consider using the EDATE Excel formula in the Excel Formula plugin. Not sure how well it works, but something to try. I…

[KPT]

I’m trying to wrap my head around your fomulars in:

@Jeff_Hager

@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.

However I for some reason can’t see how I can modify it to work with my Years and Months rather than Months and Days?

[Darren_Murphy]

@KPT

My problem is when it’s comes to Jan, Feb and March and I need to also change the year and add to the month rather than substracting?

A simple way to deal with this would be to adjust your Math formula as follows:

Year(Date) * 12
+ Month(Date)

The resultant number won’t be instantly recognisable as the year/month, but it will handle crossing over the year boundaries correctly. Essentially it’s just counting the number of months since year zero.

[Jeff_Hager]

My formula results in a date. When you place it in a math column, it should ask for two parameters, a Date, which you can replace with Now or any other date column you have…and the Months parameter, which is the number of months you want to add or subtract from that date. You could replace Months in the formula with the actual number of months you want.

My formula could be modified to get the YYYYMM result you want, but that would double the size of the formula. Instead, you could just use a second math column to convert the resulting date to YYYYMM.

Keep in mind that the Excel EDATE formula that’s also mentioned in my linked post may work too. I haven’t tried it as date math outside of native glide hasn’t always worked well in the past.

@Darren_Murphy makes a good point too, and is much simpler. Just calculate a number that represents a year and month. It doesn’t have to be recognizable as a year and month if you are just using it for a relation. My solution is more about adding a number of months to a date and getting a resulting date with the same day number. Probably a little excessive for what you need.

[KPT]

Thats a great solution - thank you

Is there a way to do similar with days.
So to check if something is yesterday or four days ago?

[Jeff_Hager]

That might be tricky since the number of days in a month can vary. There will always be 12 months in a year, but you will never have a consistent number of days in a month.

Instead, simple math can give you the number of days. just subtract a date from Now in a math column to get the number of days between the two dates. Shoot, the relative time column will do that for you by feeding it a date, but I’m not sure how useful the result would be for your use case.

[Darren_Murphy]

@Jeff_Hager

simple math can give you the number of days. just subtract a date from Now in a math column to get the number of days between the two dates.

Probably worth noting that by default that will give you a duration (hh:mm:ss), so to return the number of days you need to wrap it in a round() or trunc() function. eg Trunc(Now - Date)