Rolling time period

AFlint

I'm trying to create a report that will have a rolling time period covering some number of months. Let's say 4 months for the example.

Column 1: Current Month

Column 2: Last Month

Column 3: 2 months ago

Column 4: 3 months ago

I'm trying to user a helper column with an if statement, basically saying:

If [Date]@row is this month, 1, if [Date]@row is last month, 2, if [Date]@row is 2 months ago, 3, if [Date]@row is 3 months ago, 4

Then, I can use a countif to count each occurrence and assign to my report by month.

When I build the formula for the first condition, it works fine.

=IF([Date]@row = MONTH(TODAY()), "This Month", "Unknown")

When I create a formula to just evaluate for last month, it works fine

=IF([Date]@row = (MONTH(TODAY()) - 1), "Last Month", "Unknown")

When I try to nest the expression for last month behind this month, I get #INCORRECT ARGUMENT

=IF([Date]@row = MONTH(TODAY()), "This Month", IF([Date]@row = MONTH(TODAY()) - 1), "Last Month", "Unknown")

I can't figure out why a statement that is successful on its own fails when nested.

Any suggestions are greatly appreciated.

Jason Albrecht

Ok, thanks for the feedback @AFlint

So, while the following is clunky, it seems to work for me:

=IF(MONTH(TODAY()) = MONTH(Date@row), "This Month", IF(MONTH(TODAY()) - 1 = MONTH(Date@row), "Last Month", IF(MONTH(TODAY()) - 2 = MONTH(Date@row), "2 Months ago", IF(MONTH(TODAY()) - 3 = MONTH(Date@row), "3 Months ago", "Unknown"))))

You may want to embed this in an IFERROR function, where the field is blank?

Hope this works for you?

Jason Albrecht

Hi @AFlint

Try removing the bracket after the second TODAY()?

It looks like the second IF function is being closed off too early?

Hope this helps.

AFlint

@Jason Albrecht

Here is the modified formula- same error

=IF([Date]@row = MONTH(TODAY()), "Yes", =IF([Date]@row = MONTH(TODAY()) - 1, "Yes", "No"))

I will note that I "chased" the formula syntax in both versions, and everything was recognized correctly (logical expression, value if true, value if false) for both IF statements above, both with and without the extra ()

Thanks for trying to help

Jason Albrecht

Ok, thanks for the feedback @AFlint

So, while the following is clunky, it seems to work for me:

=IF(MONTH(TODAY()) = MONTH(Date@row), "This Month", IF(MONTH(TODAY()) - 1 = MONTH(Date@row), "Last Month", IF(MONTH(TODAY()) - 2 = MONTH(Date@row), "2 Months ago", IF(MONTH(TODAY()) - 3 = MONTH(Date@row), "3 Months ago", "Unknown"))))

You may want to embed this in an IFERROR function, where the field is blank?

Hope this works for you?

AFlint

Thank you, @Jason Albrecht ! That did work. I was even able to add one more tier for 4 or more months.

It seems the TODAY function likes to be first in the expression- I wonder if that is a syntax requirement? I'd be very curious to hear from anyone if this is the case.

I realized something today that I now need to figure out- what happens in January? At that point, MONTH(TODAY()) will be 1, but MONTH(TODAY())-1 will be 12.

Any idea how to address that?

Either way, thank you so much for your help with this.