Conditional Formatting Based on 2 Date Columns, with or without Helper Column

Mlichtenstein · 06/06/23

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!

Peggy Parchert · 06/06/23

@Mlichtenstein

@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

Eric Law · 06/06/23

Hello @Mlichtenstein you can do =IF([Membership Expiration]@row <6/29/2023, 1, 0)

Mlichtenstein · 06/06/23

https://community.smartsheet.com/discussion/comment/379105#Comment_379105

Thanks @Eric Law that gives me #INVALID OPERATION. Both the Membership Expiration and Mtg Date helper columns are formatted as dates.

Peggy Parchert · 06/06/23

@Mlichtenstein

@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

Eric Law · 06/06/23

@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.

Mlichtenstein · 06/06/23

@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!

Conditional Formatting Based on 2 Date Columns, with or without Helper Column

Best Answer

Answers

Help Article Resources

Categories