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

Mlichtenstein

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

@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

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

Mlichtenstein

Hello @Mlichtenstein<\/a> you can do =IF([Membership Expiration]@row <6\/29\/2023, 1, 0)<\/p>","bodyRaw":"[{\"insert\":\"Hello \"},{\"insert\":{\"mention\":{\"name\":\"Mlichtenstein\",\"userID\":162111}}},{\"insert\":\" you can do =IF([Membership Expiration]@row <6\\\/29\\\/2023, 1, 0)\\n\"}]","format":"rich","dateInserted":"2023-06-06T18:37:50+00:00","insertUser":{"userID":149069,"name":"Eric Law","url":"https:\/\/community.smartsheet.com\/profile\/Eric%20Law","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/LJFYWOPGFF2A\/n0RM7TH9F54SP.png","dateLastActive":"2023-07-19T21:53:17+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/379105#Comment_379105","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/379105#Comment_379105

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

Peggy Parchert

@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

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

@Eric LawI 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 ParchertThe summary field and your formula did the trick. Thank you!