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.
Answers
-
What type of column is [Invoice Month], and how is it populated?
-
it is a text/number column and I input the 3 digit abbreviation for the month (Jan, Feb, Mar, Apr, etc.).
-
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, "")
Thanks,
Aravind GP| Principal Consultant
Atturra Data & Integration
M: +61493337445
E:Aravind.GP@atturra.com
W: www.atturra.com
-
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, "")
-
@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?
-
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)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!