Formula to return a value if today is in the same month as a cell

Stwillis

Hi,

I am trying to create conditional formatting for a row if the month entered is the same as the current month. If the invoice month is July, and it is currently July, I would like to return a 1 in the Month Conditional Formatting column. Then, create a rule that turns the entire row a color if there is a 1 in the Month Conditional Formatting cell. I tried using the Excel formula in the Month Conditional Formatting column.

=IF(MONTH(Invoice month) = MONTH(TODAY()), 1, "")

but Smartsheet gives me an error.

If there is an easier way to do this, I'm open to suggestions at well.

Paul Newcome

What type of column is [Invoice Month], and how is it populated?

Stwillis

it is a text/number column and I input the 3 digit abbreviation for the month (Jan, Feb, Mar, Apr, etc.).

AravindGP

Hi @Stwillis

The MONTH function in Smartsheet looks at a date value in a date column to give you a number for the month. If the date is in January, the value you get with =MONTH([Date column]@row) will be 1. If you prefer to keep the Invoice Month column as a 3 character abbreviation, you will need a helper column with a long formula to convert Jan to 1, Feb to 2, and so on for this to work. The formula would be something like =IF([Invoice Month]@row = "Jan", 1, IF([Invoice Month]@row = "Feb", 2, etc till Dec. You can then use the formula =IF([Helper column with month number]@row = MONTH(TODAY()), 1, "")

Paul Newcome

You can use this formula to convert a three digit text "Month" into a valid number:

=FIND([Invoice Month]@row, "12JanFebMarAprMayJunJulAugSepOctNovDec") / 3

You can then incorporate this into your flag / checkbox formula like so without the need for nested IFs or helper columns:

=IF(MONTH(FIND(…….) / 3) = MONTH(TODAY()), 1, "")

Stwillis

@Paul Newcome the first part works. However, the invoice month is already in numeric form - Jan = 1, Feb = 2, etc. When I added the second formula into the sheet, I get unparseable: Where do I plug in the link to the FM of service row into the formula?

Paul Newcome

That sounds like it is different than original? I thought previously you were outputting the three character text string and not a number.

You needed to take all of the FIND that was before that formula and drop it in place of the FIND in the second formula. I removed the middle of the FIND in the second so that you could see the proper syntax.

But if you are now outputting a number instead of a text string, you don't need that solution anymore. You can just do a basic comparison.

=IF([Invoice Month]@row = MONTH(TODAY()), 1)