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.
(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.
Comments
-
Good Morning,
To get to month, I use a hidden column to assign the date code code of a cell by using "=MONTH(X)", and then I use an IF statement to define the number that is returned, "=IF(X = 1, "January", IF(X = 2, "February", IF(X = 3, "March", IF(X = 4, "April", IF(X = 5, "May", IF(X = 6, "June", IF(X = 7, "July", IF(X = 8, "August", IF(X = 9, "September", IF(X = 10, "October", IF(X = 11, "November", IF(X = 12, "December", "")))))))))))).
To get year, "=YEAR(X) -
I'm not trying to return a month name. I'm doing that using an index function and a separate date table. I'm trying to return what month & year it would be x months in the past or future, x being the modifier in my second column.
FYI - this is my month text function
=INDEX({Date Tables Range 3}, MATCH(DerivMonth5, {Date Tables Range 1}, 0))This uses my derivative month number (integers 1 - 12) and returns the text name.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 61 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!