First of all, Excel is def not my forte. And Im a newbie, just discovered this site. First post!
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.
Any help would be GREATLY appreciated!!
Conditional Formatting 2 columns
-
- NewLounger
- Posts: 2
- Joined: 03 Mar 2021, 12:49
Conditional Formatting 2 columns
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Conditional Formatting 2 columns
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.
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.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 2
- Joined: 03 Mar 2021, 12:49
Re: Conditional Formatting 2 columns
Hans,
Thank you so much. I truly appreciate. Just curious for my own sanity, how long did that take you? :)
Thank you so much. I truly appreciate. Just curious for my own sanity, how long did that take you? :)
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Conditional Formatting 2 columns
It took me about 5 minutes - I've answered many similar questions over the years...
Best wishes,
Hans
Hans
-
- Panoramic Lounger
- Posts: 8185
- Joined: 25 Jan 2010, 09:09
- Location: retirement
Re: Conditional Formatting 2 columns
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
=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.
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Conditional Formatting 2 columns
Best wishes,
Hans
Hans
-
- Panoramic Lounger
- Posts: 8185
- Joined: 25 Jan 2010, 09:09
- Location: retirement
Re: Conditional Formatting 2 columns
an but, as yet no flaw reported...
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Conditional Formatting 2 columns
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.
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
Hans