Conditional Formatting Based on 2 Date Columns, with or without Helper Column
Hi! I've scoured the Community posts, and can't find exactly what I need. I am managing a membership list and need to flag when memberships expire before or on a specific date (06/29/2023) so I can contact those members. I have one column with their membership expiration dates, a second column with the 06/29/2023 deadline, and I can have a third column as a helper if needed.
Appreciate any help!
Best Answer
-
@Eric Law - you are speedy 😊
Another option would be to us a Sheet Summary field for the Expiration Helper Column - Mtg Date instead of a column in the sheet.
Flag column formula: =IFERROR(IF([Membership Expiration]@row <= [Expiration Helper Column - Mtg Date]#, 1, 0), "No Date")
Sheet Summary field (Expiration Helper Column - Mtg Date would equal 6/29/23
Hope this helps.
Thanks -Peggy
Answers
-
Hello @Mlichtenstein you can do =IF([Membership Expiration]@row <6/29/2023, 1, 0)
-
Thanks @Eric Law that gives me #INVALID OPERATION. Both the Membership Expiration and Mtg Date helper columns are formatted as dates.
-
@Eric Law - you are speedy 😊
Another option would be to us a Sheet Summary field for the Expiration Helper Column - Mtg Date instead of a column in the sheet.
Flag column formula: =IFERROR(IF([Membership Expiration]@row <= [Expiration Helper Column - Mtg Date]#, 1, 0), "No Date")
Sheet Summary field (Expiration Helper Column - Mtg Date would equal 6/29/23
Hope this helps.
Thanks -Peggy
-
@Mlichtenstein Will you be updating the helper column? Or is this a one off. You should be able to just put that formula above in the Formula column.
-
@Eric Law I won't be updating the helper column, but even when copying/pasting that into the formula column, I'm still getting the #invalid operation error. I appreciate your responses!
@Peggy Parchert The summary field and your formula did the trick. Thank you!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.9K Get Help
- 410 Global Discussions
- 220 Industry Talk
- 457 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!