Conditional Formatting 2 columns

LookieHere
NewLounger
Posts: 2
Joined: 03 Mar 2021, 12:49

Conditional Formatting 2 columns

Post by LookieHere »

First of all, Excel is def not my forte. And Im a newbie, just discovered this site. First post! :fanfare:

I am a member of a private social club.
I need help calculating when a member is eligible for lifetime membership.


Requirements:
When a member reaches age 60 (DOB)
and
They have been a member for 25 years (JOIN DATE)
We have over 800 members that I have yet to enter their information.

I don’t know if it would be easier to highlight the cells when those 2 criteria’s are met or maybe another column showing lifetime member eligibility date? The second option would be preferred.
There is no sensitive information in the spreadsheet. Ive been trying to figure this out but I just cant get it done. :sad:

Any help would be GREATLY appreciated!!
MemberListInfo.xlsx
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78231
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Conditional Formatting 2 columns

Post by HansV »

Welcome to Eileen's Lounge.

Insert a new column in the table, for example to the right of the JOIN DATE column.
Name this column ELIGIBLE or similar.
Enter the following formula in row 2 of the new column:

=IF([@DOB]="","",AND(DATEDIF([@DOB],TODAY(),"Y")>=60,DATEDIF([@[JOIN DATE]],TODAY(),"Y")>=25))

Excel should automatically fill the entire column with this formula.

You can easily filter the column for TRUE or FALSE.

See the attached version.

MemberListInfo.xlsx
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

LookieHere
NewLounger
Posts: 2
Joined: 03 Mar 2021, 12:49

Re: Conditional Formatting 2 columns

Post by LookieHere »

Hans,
Thank you so much. I truly appreciate. Just curious for my own sanity, how long did that take you? :)

User avatar
HansV
Administrator
Posts: 78231
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Conditional Formatting 2 columns

Post by HansV »

It took me about 5 minutes - I've answered many similar questions over the years...
Best wishes,
Hans

User avatar
stuck
Panoramic Lounger
Posts: 8125
Joined: 25 Jan 2010, 09:09
Location: retirement

Re: Conditional Formatting 2 columns

Post by stuck »

Here's an 'old school' version that doesn't use a Table and colours the rows using the conditional formatting rule:
=AND(DATEDIF($G2,TODAY(),"y")>=60,DATEDIF($H2,TODAY(),"y")>=25)
that applies to the range:
=$A$2:$H$7

Ken
edited to add: this where Hans points out the flaw in my version
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78231
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Conditional Formatting 2 columns

Post by HansV »

:evilgrin:
Best wishes,
Hans

User avatar
stuck
Panoramic Lounger
Posts: 8125
Joined: 25 Jan 2010, 09:09
Location: retirement

Re: Conditional Formatting 2 columns

Post by stuck »

:hmmn: an :evilgrin: but, as yet no flaw reported...

User avatar
HansV
Administrator
Posts: 78231
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Conditional Formatting 2 columns

Post by HansV »

Your conditional formatting formula works perfectly.
The only difference with the formula that I proposed is that I added a condition to avoid reporting/highlighting rows where the date of birth hasn't been entered yet.
Best wishes,
Hans