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

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.

Find more posts tagged with

Accepted answers

All comments

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)

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.