Helper column formula to identify if "Month Requested" was the previous month

Christina C

Hi!

I really thought I had this one but I am getting an INCORRECT ARGUEMENT error.

I have created a help column to flag if the "month requested" value in my sheet is the Previous Month. I need to flag rows that are previous month to use in a filter for a report as I will be sending the report out the first day of the month for the previous month automatically.

Here's my formula:

=IF(MONTH([Month Requested]@row = MONTH(TODAY()) - 1, "Previous Month", ""))

If the Month Requested is the previous month I would like to see "Previous Month" returned, otherwise it would be blank.

What am I doing wrong? Thanks!

Cory Page

Good morning

Would something like this work for you?

=IF(MONTH(testing@row) - MONTH(TODAY(-1)) < 0, "Previous Month", "")

JamesB

@Christina C You are close, just have a slight error in your formula.

Your Formula:

=IF(MONTH([Month Requested]@row = MONTH(TODAY()) - 1, "Previous Month", ""))

Corrected Formula:

=IF(MONTH([Month Requested]@row) = MONTH(TODAY()) - 1, "Previous Month", "")

Christina C

@Cory Page & @JamesB - thank you both. But neither worked and both returned an "Invalid Date Type" error.

Could the issue be that the Month Reuested colum is a formula as well and can't be referenced in another formula?

The Month Requested column is calculated by =MONTH([Date Submitted]@row). I wish there was just a Previous Month function.

JamesB

@Christina C

Date Formulas need to reference date type columns.

This looks like you already have the Month Number in the Month Requested Column.

There are 2 ways to correct this.

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

Or

=IF([MONTH REQUESTED]@row = Month(TODAY())-1,"Previous Month", "")

Christina C

The "Month Requested" column is Text/Number because it has the formula =MONTH([Date Submitted]@row in it. It returns the number value of the month, 7, 8 etc.

Paul Newcome

You are going to need something like this if you plan on flagging December rows when the "current month" is January:

=IF([Month Requested]@row = IF(MONTH(TODAY()) = 1, 12, MONTH(TODAY()) - 1), "Previous Month")

This will flag all rows regardless of year though. If you need to incorporate the year, we would need to adjust further.

Christina C

Hi @Paul Newcome - this worked! I am getting my desired result. I do remember reading something in another thread about how to handle December so thank you, this takes that into cosideration.

I'm thinking about your comment "This will flag all rows regardless of year though. If you need to incorporate the year, we would need to adjust further." This will be an issue as when the report in July 2024 is generated, I don't want it to include June 2023 and June 2024 information. At this point in the development of this solution, we have not decided if we will archive previous year requests or keep a running list. With that said, I think I will need to adjust for the year as well.

Will I need another helper column for year like I have for Month Requested and then adjust the formula to take the year into consideration?

So I have added the new columnnow. Does the formula become and IF(AND.... now?

Paul Newcome

Really you don't need any helper columns as you can reference the month and year directly from the date.

=IF(AND(MONTH([Date Column Name]@row = IF(MONTH(TODAY()) = 1, 12, MONTH(TODAY()) - 1), YEAR([Date Column Name]@row) = YEAR(TODAY()) - IF(MONTH(TODAY()) = 1, 1, 0)), "Previous Month")

Christina C

Hi @Paul Newcome ....I was thinking that as well but my "Date Column" is the system "Date Submitted" field which I think you can't use because of the date and time stamp? Hence I thought I had to use the helper columns? Does that make sense?

Paul Newcome

It should still be fine. If it gives you any trouble, we can incorporate a DATEONLY function, but really we shouldn't need it.

If you think about it...

=MONTH([Date Created]@row)

is going to work the same regardless of whether it is outputting its data in a cell as a standalone function or it is outputting its data to be further processed by another function.

Christina C

@Paul Newcome - I think I am getting close but I'm getting errors.

I tried 2 different formulas, one using the "Date Submitted" column only and the other using the helper columns. Maybe I'm missing a parenthsis somewhere?

In the sheet shot below, on row 5 I tried the following and get the incorrect arguement error:

=IF(AND(MONTH([Month Requested]@row = IF(MONTH(TODAY()) = 1, 12, MONTH(TODAY()) - 1), YEAR([Year Requested]@row) = YEAR(TODAY()) - IF(MONTH(TODAY()) = 1, 1, 0)), "Previous Month"))

In row 6 I try to use just the date submitted field as you suggested:

=IF(AND(MONTH([Date Submitted]@row = IF(MONTH(TODAY()) = 1, 12, MONTH(TODAY()) - 1), YEAR([Date Submitted]@row) = YEAR(TODAY()) - IF(MONTH(TODAY()) = 1, 1, 0)), "Previous Month"))

Row 7 just has the month and isn't looking at the year but it works:

=IF([Month Requested]@row = IF(MONTH(TODAY()) = 1, 12, MONTH(TODAY()) - 1), "Previous Month")

Row 8 I try to use the Date Submitted field and I get an Invalid Operationerror which leads me to believe I can't use the Date Submitted field as is.

Really appreiciate you help with this. I wish there was just a previous month function!

Paul Newcome

Sorry about that. I missed a closing parenthesis.

=IF(AND(MONTH([Date Column Name]@row) = IF(MONTH(TODAY()) = 1, 12, MONTH(TODAY()) - 1), YEAR([Date Column Name]@row) = YEAR(TODAY()) - IF(MONTH(TODAY()) = 1, 1, 0)), "Previous Month")

Christina C

@Paul Newcome - No apoligies needed, I should have caught that.....it worked! And I used the system date submitted with no issues and not my helper columns so this is great.

I really appreciate your going back and forth with me this! Many thanks.

Paul Newcome

Happy to help. 👍️