Prior Month Month/Year Formula Crossing Over 2 Years

Allison Horn

Looking for some guidance on a formula. I am needing to collect a quick snapshot of Data for the following:

• Current Month - based on today's month + current year
• Prior Month - based on the month prior to today's month + year.

Given that we are in January, the current month is populating correctly with the formula I created. I was able to create a formula for the Prior Month with the prior year, however, once we move into February, we're going to have an issue as PM1 should read January 2023, but the way I have the formula written it's only pulling PY. I am stumped on how I can improve so it's not a manual update in the control center to all of the formulas and it be effective to it can span across years.

A few thoughts:

• Can i populate the Prior Month based on the Current Month and just work my way back?
• Is there an alternative formula than the below to capture the current year through an "If statement" for Months October - December that I can use in Prior Month 1, 2 and 3?

Formula text:

=IF(MONTH(TODAY()) = 1, "December", IF(MONTH(TODAY()) = 2, "January", IF(MONTH(TODAY()) = 3, "February", IF(MONTH(TODAY()) = 4, "March", IF(MONTH(TODAY()) = 5, "April", IF(MONTH(TODAY()) = 6, "May", IF(MONTH(TODAY()) = 7, "June", IF(MONTH(TODAY()) = 8, "July", IF(MONTH(TODAY()) = 9, "August", IF(MONTH(TODAY()) = 10, "September", IF(MONTH(TODAY()) = 11, "October", IF(MONTH(TODAY()) = 12, "November")))))))))))) + " " + (YEAR(TODAY()) - 1)

Genevieve P.

Hi @Allison Horn

The way I'd do this is to add another IF statement at the end to check the current month and determine if the year should be last year or the current year.

For example, with your Prior Month 1 column, try:

=IF(MONTH(TODAY()) = 1, "December", IF(MONTH(TODAY()) = 2, "January", IF(MONTH(TODAY()) = 3, "February", IF(MONTH(TODAY()) = 4, "March", IF(MONTH(TODAY()) = 5, "April", IF(MONTH(TODAY()) = 6, "May", IF(MONTH(TODAY()) = 7, "June", IF(MONTH(TODAY()) = 8, "July", IF(MONTH(TODAY()) = 9, "August", IF(MONTH(TODAY()) = 10, "September", IF(MONTH(TODAY()) = 11, "October", IF(MONTH(TODAY()) = 12, "November")))))))))))) + " " + IF(MONTH(TODAY()) = 1, YEAR(TODAY()) - 1, YEAR(TODAY()))

For Prior Month 2, you'll want to check if it's January or February today:

=IF(MONTH(TODAY()) = 1, "December", IF(MONTH(TODAY()) = 2, "January", IF(MONTH(TODAY()) = 3, "February", IF(MONTH(TODAY()) = 4, "March", IF(MONTH(TODAY()) = 5, "April", IF(MONTH(TODAY()) = 6, "May", IF(MONTH(TODAY()) = 7, "June", IF(MONTH(TODAY()) = 8, "July", IF(MONTH(TODAY()) = 9, "August", IF(MONTH(TODAY()) = 10, "September", IF(MONTH(TODAY()) = 11, "October", IF(MONTH(TODAY()) = 12, "November")))))))))))) + " " + IF(OR(MONTH(TODAY()) = 1, MONTH(TODAY()) = 2), YEAR(TODAY()) - 1, YEAR(TODAY()))

For Month 3, check all three months:

=IF(MONTH(TODAY()) = 1, "December", IF(MONTH(TODAY()) = 2, "January", IF(MONTH(TODAY()) = 3, "February", IF(MONTH(TODAY()) = 4, "March", IF(MONTH(TODAY()) = 5, "April", IF(MONTH(TODAY()) = 6, "May", IF(MONTH(TODAY()) = 7, "June", IF(MONTH(TODAY()) = 8, "July", IF(MONTH(TODAY()) = 9, "August", IF(MONTH(TODAY()) = 10, "September", IF(MONTH(TODAY()) = 11, "October", IF(MONTH(TODAY()) = 12, "November")))))))))))) + " " + IF(OR(MONTH(TODAY()) = 1, MONTH(TODAY()) = 2, MONTH(TODAY()) = 3), YEAR(TODAY()) - 1, YEAR(TODAY()))

Let me know if that makes sense and works for you!

Cheers,

Genevieve

Allison Horn

That worked, thank you. Made a few changes since I had to back into the prior months otherwise the formulas were giving me December 2022 for all of them.

Prior Month 1

=IF(MONTH(TODAY()) = 1, "December", IF(MONTH(TODAY()) = 2, "January", IF(MONTH(TODAY()) = 3, "February", IF(MONTH(TODAY()) = 4, "March", IF(MONTH(TODAY()) = 5, "April", IF(MONTH(TODAY()) = 6, "May", IF(MONTH(TODAY()) = 7, "June", IF(MONTH(TODAY()) = 8, "July", IF(MONTH(TODAY()) = 9, "August", IF(MONTH(TODAY()) = 10, "September", IF(MONTH(TODAY()) = 11, "October", IF(MONTH(TODAY()) = 12, "November")))))))))))) + " " + IF(MONTH(TODAY()) = 1, YEAR(TODAY()) - 1, YEAR(TODAY()))

Prior Month 2

=IF(MONTH(TODAY()) = 1, "November", IF(MONTH(TODAY()) = 2, "December", IF(MONTH(TODAY()) = 3, "January", IF(MONTH(TODAY()) = 4, "February", IF(MONTH(TODAY()) = 5, "March", IF(MONTH(TODAY()) = 6, "April", IF(MONTH(TODAY()) = 7, "May", IF(MONTH(TODAY()) = 8, "June", IF(MONTH(TODAY()) = 9, "July", IF(MONTH(TODAY()) = 10, "August", IF(MONTH(TODAY()) = 11, "September", IF(MONTH(TODAY()) = 12, "October")))))))))))) + " " + IF(OR(MONTH(TODAY()) = 1, MONTH(TODAY()) = 2), YEAR(TODAY()) - 1, YEAR(TODAY()))

Prior Month 3

=IF(MONTH(TODAY()) = 1, "October", IF(MONTH(TODAY()) = 2, "November", IF(MONTH(TODAY()) = 3, "Decemeber", IF(MONTH(TODAY()) = 4, "January", IF(MONTH(TODAY()) = 5, "February", IF(MONTH(TODAY()) = 6, "March", IF(MONTH(TODAY()) = 7, "April", IF(MONTH(TODAY()) = 8, "May", IF(MONTH(TODAY()) = 9, "June", IF(MONTH(TODAY()) = 10, "July", IF(MONTH(TODAY()) = 11, "August", IF(MONTH(TODAY()) = 12, "September")))))))))))) + " " + IF(OR(MONTH(TODAY()) = 1, MONTH(TODAY()) = 2, MONTH(TODAY()) = 3), YEAR(TODAY()) - 1, YEAR(TODAY()))

I do have one more question @Genevieve P. that you may be able to assist with :)

Now that I have those reference cells, If I wanted to run an Index match across parent and children data, what is the best way to do that? I have three years worth of Data; each have duplicated metrics below those years so I need to take a Prior Month (i.e. December 2022) and look in that year and find the Production Goal. My formula for index match works find in the nestled section but lost on how I do it to span across parent/children relationships. Is it an Index Collect?

Allison Horn

Now I am wondering if a If then Index statement might be better, not sure if that is possible.

If Prior Month 1 contains last year, index production goal for 2022? I have the index down, but trying to figure out referencing December 2022 as the cell for the if statement.

@Paul Newcome - You're a magician, any thoughts on this formula? It may be long but I am thinking it could work? Maybe there is a better and more efficient solution too.

Paul Newcome

EDIT: I just saw where you already have that top row taken care of. If you are planning on going more than 3 months prior, feel free to look a the below. Otherwise... Skip this comment entirely, and I will work on the other piece in a separate comment. I was thinking you needed to expand that top row indefinitely. 🤦‍♂️

I feel like we should be able to adapt my EDATE solution, but... It is a rather lengthy formula that you will NOT want to repeat a bunch of times in a nested IF. My suggestion would be to create a single column table and use an INDEX function to pull in the month text based on the month output.

Jan

Feb

Mar

Apr

so on and so forth...

Then use

=INDEX({Month Text Column}, MONTH(edate_solution)) + " " + YEAR(edate_solution)

The trickier part though is that my EDATE solution relies on you putting a number of months into a separate column, so we will have to replace [Number of Months]@row with a COUNT that will adjust the range as you dragfill to count how far away from [Current Month]@row we are (note: very important to lock in the first reference and leave the second open).

COUNT($[Current Month]@row:[Current Month]@row)

Locking the first and leaving the second open means that when you dragfill from -1 month to -2 months, the formula will automatically update the range to be

COUNT($[Current Month]@row:[Prior Month 1]@row)

So now that we have the number of months portion automated, lets drop that COUNT in to the EDATE formula.

Base EDATE (from today):

=IFERROR(DATE(YEAR(TODAY()) + ROUNDDOWN((MONTH(TODAY()) + [Number Of Months]@row) / 12, 0) + IF(IF(MOD(MONTH(TODAY()) + [Number Of Months]@row, 12) = 0, 12, MOD(MONTH(TODAY()) + [Number Of Months]@row, 12)) = 12, -1) - IF(AND(ABS([Number Of Months]@row) - MONTH(TODAY()) <> 12, [Number Of Months]@row < 0, ABS([Number Of Months]@row) > MONTH(TODAY())), 1, 0), IF(MOD(MONTH(TODAY()) + [Number Of Months]@row, 12) = 0, 12, MOD(MONTH(TODAY()) + [Number Of Months]@row, 12)), 1), DATE(IF(MONTH(TODAY()) - ABS([Number Of Months]@row) < 1, YEAR(TODAY()) - 1, YEAR(TODAY())), IF(MONTH(TODAY()) - ABS([Number Of Months]@row) < 1, MONTH(TODAY()) + (12 - ABS([Number Of Months]@row)), MONTH(TODAY()) - ABS([Number Of Months]@row)), 1))

Automated Number of Months:

=IFERROR(DATE(YEAR(TODAY()) + ROUNDDOWN((MONTH(TODAY()) + COUNT($[Current Month]@row:[Prior Month 1]@row)) / 12, 0) + IF(IF(MOD(MONTH(TODAY()) + COUNT($[Current Month]@row:[Prior Month 1]@row), 12) = 0, 12, MOD(MONTH(TODAY()) + COUNT($[Current Month]@row:[Prior Month 1]@row), 12)) = 12, -1) - IF(AND(ABS(COUNT($[Current Month]@row:[Prior Month 1]@row)) - MONTH(TODAY()) <> 12, COUNT($[Current Month]@row:[Prior Month 1]@row) < 0, ABS(COUNT($[Current Month]@row:[Prior Month 1]@row)) > MONTH(TODAY())), 1, 0), IF(MOD(MONTH(TODAY()) + COUNT($[Current Month]@row:[Prior Month 1]@row), 12) = 0, 12, MOD(MONTH(TODAY()) + COUNT($[Current Month]@row:[Prior Month 1]@row), 12)), 1), DATE(IF(MONTH(TODAY()) - ABS(COUNT($[Current Month]@row:[Prior Month 1]@row)) < 1, YEAR(TODAY()) - 1, YEAR(TODAY())), IF(MONTH(TODAY()) - ABS(COUNT($[Current Month]@row:[Prior Month 1]@row)) < 1, MONTH(TODAY()) + (12 - ABS(COUNT($[Current Month]@row:[Prior Month 1]@row))), MONTH(TODAY()) - ABS(COUNT($[Current Month]@row:[Prior Month 1]@row))), 1))

Now that we have that, we can drop it in to the formula that indexes the month text and outputs the year.

=INDEX({Month Text Column}, MONTH(edate_solution)) + " " + YEAR(edate_solution)

(Hopefully) Final Solution 🤞 (use your original formula for [Current Month] or update to a basic INDEX function to pull in the month text / the below would go in [Prior Month 1] and then dragfill to the right as far as needed):

=INDEX({Month Text Column}, MONTH(IFERROR(DATE(YEAR(TODAY()) + ROUNDDOWN((MONTH(TODAY()) + COUNT($[Current Month]@row:[Prior Month 1]@row)) / 12, 0) + IF(IF(MOD(MONTH(TODAY()) + COUNT($[Current Month]@row:[Prior Month 1]@row), 12) = 0, 12, MOD(MONTH(TODAY()) + COUNT($[Current Month]@row:[Prior Month 1]@row), 12)) = 12, -1) - IF(AND(ABS(COUNT($[Current Month]@row:[Prior Month 1]@row)) - MONTH(TODAY()) <> 12, COUNT($[Current Month]@row:[Prior Month 1]@row) < 0, ABS(COUNT($[Current Month]@row:[Prior Month 1]@row)) > MONTH(TODAY())), 1, 0), IF(MOD(MONTH(TODAY()) + COUNT($[Current Month]@row:[Prior Month 1]@row), 12) = 0, 12, MOD(MONTH(TODAY()) + COUNT($[Current Month]@row:[Prior Month 1]@row), 12)), 1), DATE(IF(MONTH(TODAY()) - ABS(COUNT($[Current Month]@row:[Prior Month 1]@row)) < 1, YEAR(TODAY()) - 1, YEAR(TODAY())), IF(MONTH(TODAY()) - ABS(COUNT($[Current Month]@row:[Prior Month 1]@row)) < 1, MONTH(TODAY()) + (12 - ABS(COUNT($[Current Month]@row:[Prior Month 1]@row))), MONTH(TODAY()) - ABS(COUNT($[Current Month]@row:[Prior Month 1]@row))), 1)))) + " " + YEAR(IFERROR(DATE(YEAR(TODAY()) + ROUNDDOWN((MONTH(TODAY()) + COUNT($[Current Month]@row:[Prior Month 1]@row)) / 12, 0) + IF(IF(MOD(MONTH(TODAY()) + COUNT($[Current Month]@row:[Prior Month 1]@row), 12) = 0, 12, MOD(MONTH(TODAY()) + COUNT($[Current Month]@row:[Prior Month 1]@row), 12)) = 12, -1) - IF(AND(ABS(COUNT($[Current Month]@row:[Prior Month 1]@row)) - MONTH(TODAY()) <> 12, COUNT($[Current Month]@row:[Prior Month 1]@row) < 0, ABS(COUNT($[Current Month]@row:[Prior Month 1]@row)) > MONTH(TODAY())), 1, 0), IF(MOD(MONTH(TODAY()) + COUNT($[Current Month]@row:[Prior Month 1]@row), 12) = 0, 12, MOD(MONTH(TODAY()) + COUNT($[Current Month]@row:[Prior Month 1]@row), 12)), 1), DATE(IF(MONTH(TODAY()) - ABS(COUNT($[Current Month]@row:[Prior Month 1]@row)) < 1, YEAR(TODAY()) - 1, YEAR(TODAY())), IF(MONTH(TODAY()) - ABS(COUNT($[Current Month]@row:[Prior Month 1]@row)) < 1, MONTH(TODAY()) + (12 - ABS(COUNT($[Current Month]@row:[Prior Month 1]@row))), MONTH(TODAY()) - ABS(COUNT($[Current Month]@row:[Prior Month 1]@row))), 1)))

Paul Newcome

To pull in from across the columns on each row, you should be able to use an INDEX/MATCH except the MATCH would go in the column number portion instead of the more typical row number portion of the INDEX function.

=INDEX($January@row:$December@row, 1, MATCH([Current Month]$1, $January$1:$December$1, 0))

Genevieve P.

Wow, thanks for stepping in, @Paul Newcome!

My apologies for missing adjusting the Month portion of the start of the formulas for your other columns, @Allison Horn - you're absolutely correct. I was just focussed on updating the year at the end haha.

Paul Newcome

@Genevieve P. Haha. I totally misread though and went WAY beyond what was needed in that first comment. 🤣

Allison Horn

@Paul Newcome the below formula doesn't work because I am trying to look across three rows (under the years) and it's duplicated names. Should I be using an Index Match column to look up/reference?

I used the formula and you can see in the Prior Month 1 column that ishighlighted it is giving me a $0 and it should be $124,583

Paul Newcome

Ah. Ok. I see what you have going on now. Sorry about that. That definitely gets a little trickier. May have to play with this one a little bit...

@Genevieve P. and @Allison Horn I am going to end up putting in an enhancement request to be able to use ranges in the hierarchy functions.

PARENT(January@row):PARENT(December@row)

or

PARENT(January@row:December@row)

One or the other would make this so much simpler. I will include a link to this thread as a use case and drop a link to the enhancement request here in case anyone would be interested in voting on it.

In the meantime, unless Genevieve beats me to it, I am going to have to do some testing in my own sheet. I have a couple of ideas that MIGHT work but still need to work out the details to be sure.

Allison Horn

Unfortunately, that didn't work. It reads #Unparesable

=index(PARENT(January@row:December@row),1,match([Prior Month 1]54, $january$1:$december$159,0))

Is there a way to pull it based on two conditions or add an if statement?

Based on the Prior Month 1 cell (yellow) year, could we have it go find that year in my year column and then grab the December info in that same metric row? Or, I could change the Index Match Formula to include the Metric Name + Prior Month Reference (December 2022) and have that duplicated in the 2022 data, which matches that.

For example, instead of it saying "Production Goal ($) 2022," it might say "Production Goal ($) December 2022," and that is the match

Paul Newcome

@Allison Horn Yes. It does read as UNPARESEABLE because Smartsheet can't read my mind and build things in before I need them. Haha. I had those up there to show what I was going to put in the enhancement request.

I am still plugging away on testing other options as time permits. That YEAR column definitely helps.

Allison Horn

oh, gotcha! :)

Appreciate the help.

Paul Newcome

Let's give this a try...

We will INDEX/COLLECT based on production goal and year being equal to the year in the parent.

=INDEX(COLLECT(January:December, Metric:Metric, @cell = Metric@row, Year:Year, @cell = VALUE(RIGHT(INDEX(ANCESTORS(), 1), 4)), 1, nested IF)

For the nested IF portion, I am thinking we could strip the month out of the parent and say if that equals "January", 1, if it equals "February", 2, so on and so forth. The bold portion below is what grabs just the month.

IF(LEFT(INDEX(ANCESTORS(), 1), LEN(INDEX(ANCESTORS(), 1)) - 5) = "January", 1, IF(LEFT(INDEX(ANCESTORS(), 1), LEN(INDEX(ANCESTORS(), 1)) - 5) = "February", 2, IF(LEFT(INDEX(ANCESTORS(), 1), LEN(INDEX(ANCESTORS(), 1)) - 5) = "March", 3, ...........................................................................

Then the full formula would be

=INDEX(COLLECT(January:December, Metric:Metric, @cell = Metric@row, Year:Year, @cell = VALUE(RIGHT(INDEX(ANCESTORS(), 1), 4)), 1, IF(LEFT(INDEX(ANCESTORS(), 1), LEN(INDEX(ANCESTORS(), 1)) - 5) = "January", 1, IF(LEFT(INDEX(ANCESTORS(), 1), LEN(INDEX(ANCESTORS(), 1)) - 5) = "February", 2, IF(LEFT(INDEX(ANCESTORS(), 1), LEN(INDEX(ANCESTORS(), 1)) - 5) = "March", 3, ...........................................................................)))))))))))))

This should also allow you to make it a column formula, and you should be able to use this formula without modification in each of your three columns (current prior 1 and prior 2).

Allison Horn

I think I did it right, but getting an Incorrect argument.

=INDEX(COLLECT(January:December, Metric:Metric, @cell = Metric@row, Year:Year, @cell = VALUE(RIGHT(INDEX(ANCESTORS(), 1), 4)), 1, IF(LEFT(INDEX(ANCESTORS(), 1), LEN(INDEX(ANCESTORS(), 1)) - 5) = "January", 1, IF(LEFT(INDEX(ANCESTORS(), 1), LEN(INDEX(ANCESTORS()) - 5) = "February", 2)))))

Paul Newcome

It looks like the INDEX/ANCESTORS from the "February" portion is missing the "row" piece for the INDEX function and the closing parenthesis for that same LEN function is out of place. And I missed closing out the COLLECT function in the right place.

For the below, we should only have 3 closing parenthesis at the end of the formula. 2 for the nested IF and 1 for the overall INDEX.

=INDEX(COLLECT(January:December, Metric:Metric, @cell = Metric@row, Year:Year, @cell = VALUE(RIGHT(INDEX(ANCESTORS(), 1), 4))), 1, IF(LEFT(INDEX(ANCESTORS(), 1), LEN(INDEX(ANCESTORS(), 1)) - 5) = "January", 1, IF(LEFT(INDEX(ANCESTORS(), 1), LEN(INDEX(ANCESTORS(), 1)) - 5) = "February", 2)))

For future trouble shooting...

=INDEX(COLLECT(.......................), row#, column#)

A) Range to collect is January through December columns.

B) First range/criteria set for the COLLECT is Metric column where it equals Metric@row.

C) Second range/criteria set is Year column where the cell is equal to the year in the top parent row.

C.1) To get the top parent we use: INDEX(ANCESTORS(), 1)

C.2) To get the year out of that we use the RIGHT function to pull the 4 rightmost characters: RIGHT(cell, 4)

C.3) We convert that into a number on the assumption that the values in the Year column are stored as numeric values: VALUE(RIGHT(..........)

D) The row portion of the initial INDEX function is 1 since we should in theory only be pulling a single row.

E) The column portion is a nested IF converting the month text in the top parent into a number: IF(cell = "Jan", 1, IF(cell = "Feb", 2))

E.1) We use the same thing to grab the top parent: INDEX(ANCESTORS(), 1)

E.2) We use the LEFT function to grab the month text: LEFT(cell, #)

E.3) We use the character count of the cell minus 5 (4 digit year plus the space) to determine how many characters we pull for the LEFT function: LEN(cell) - 5

So working from the inside out on the nested IF we have

LEN(cell) - 5

LEFT(cell, LEN(cell) - 5)

In the above, each place we see "cell", we drop in the INDEX/ANCESTORS.

LEN(INDEX(ANCESTORS(), 1)) - 5

LEFT(INDEX(ANCESTORS(), 1), LEN(INDEX(ANCESTORS(), 1)) - 5)

If that equals "January", output 1.

IF(LEFT(INDEX(ANCESTORS(), 1), LEN(INDEX(ANCESTORS(), 1)) - 5) = "January", 1