Ok. so I've set up a rolling dashboard built around the today function - something like this. Everything rolls over automatically as it all references TODAY() to determine the various derivatives.
(see screenshot)
It's great, but doesn't scale (yet). The Month column uses this right now -
=IF((MONTH(TODAY()) + Modifier5) > 12, MONTH(TODAY()) + Modifier5 - 12, IF(MONTH(TODAY()) + Modifier5 <= 0, MONTH(TODAY()) + Modifier5 + 12, MONTH(TODAY()) + Modifier5))
Basically, if the derivative month goes over 12, subtract 12 so I can give use this column in a
DATE(year_reference, month_reference, day_reference) function for reporting further down.
I want to scale it for more than just the next (or last) year but keep running into errors, primarily around December - My current formula is this:
=INT(YEAR(TODAY()) + ((Modifier3 + MONTH(TODAY())) / 12)) to give me year
=MONTH(TODAY()) - ((DerivYear3 - YEAR(TODAY())) * 12 - Modifier3) to give me month
It works for any month except December. December returns the month as 0 and clicks year up a month early.
The problem is the underlined phrase. If the derived month is December (12), it equals a whole number and clicks the year over early. I'm trying to figure out an approach that will work.
Thank you.
