Using a modifier to derive month (formula de-bugging help
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.
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.
Help Article Resources
Check out the Formula Handbook template!