Highlight cells when a person is younger than 55 years, based on personal numbers

[sofiachr]

Hi,

I have a column with personal numbers with the format YYMMDD-XXXX. In some cases there are 2 personal numbers in the same cell and then the format is YYMMDD-XXXX / YYMMDD-XXXX. I want to highlight all cells in this column (column C) where the person is younger than 55 years. The personal numbers start at row 2 (row 1 are the headlines). I make it work with dates but not with personal numbers and also the tricky part is making the cells with two personal numbers work too. In these cases the cell should be highlighted only if both of the persons are under 55 years old. Ofcourse I could make these cells by hand if it´s too hard so firstly I would appreciate help to get the normal cells with only one personal number to work.
Thanks!

Sincerely, Sofia

 

[steve the fish]

Check this works for one number:

=DATE(IF(LEFT(A1,2)>30,19,20)&LEFT(A1,2),MID(A1,3,2),MID(A1,5,2))>=EDATE(TODAY(),-55*12)

It has to make an assumption because you dont use 4 digit years. If greater than 30 the year is 1900s. If less than 31 the year is 2000s.

 

[Gerald Higgins]

Or this for one number . . .

=IF(DATE(LEFT(A1,2)+0,MID(A1,3,2)+0,MID(A1,5,2)+0)>(TODAY()-(55*365.25)),TRUE,)

and this for two . . .

=IF(DATE(LEFT(A1,2)+0,MID(A1,3,2)+0,MID(A1,5,2)+0)>(TODAY()-(55*365.25)),IF(LEN(A1)>20,IF(DATE(MID(A1,15,2)+0,MID(A1,17,2)+0,MID(A1,19,2)+0)>(TODAY()-(55*365.25)),TRUE,),TRUE),)

COMMENTS
Uses 365.25 days as the length of a year, maybe this is not accurate enough for you.
Compares date of birth against today's date. So anyone who has their 55th birthday tomorrow will stop being highlighted tomorrow.
Also has the problem that Steve referred to, about using 2 digit years. My version does not correct for that, and treats a year 18 as if it is 1918, not 2018. I suggest you use 4 digit years to avoid this problem, and adapt the formula to suit. OR, if everyone in your dataset is more than 20 years old (and you're not going to use it after this year), then it's not a problem.
Assumes your DOB and personal numbers are formated EXACTLY as described. Any variation in length etc will cause the formula to fail.

 

[sofiachr]

Hi Steve,
It accepts the formula but something seems wrong because it doesn´t highlight the cell.
Sofia

 

[sofiachr]

Hi Gerald,

Tried this and it´s very similar to Steves but I can´t make it work. Don´t know why.
Sofia

 

[Gerald Higgins]

So I got mine to work in Conditional Formating (CF).

Let's say your date and code is in cell A1 - if it's not in A1, adapt the formula as required.
Choose the CF option for "Use a formula to determine which cells to format".

Enter the formula into the box for "Format values where this formula is true:"

And pick a format, such as a yellow cell background.

Should work.

Are the DOB / Codes entered in EXACTLY the format you described ?
Can you give us a specific example of a DOB / Code you are using


If you don't want to use CF, you can also just enter my formula somewhere on the worksheet, when it should return TRUE if the condition is met, i.e. the one single, or both, dates are less than 55 years old.
If it does NOT return TRUE, you can use Excel's Formula, Evaluate function to check where it's going wrong.

 

[sofiachr]

Now I got it to work. The problem was that I tried entering 190101-1234 as a personal number but it needed to be the 1900s to work. I will change to 4 figures to make it work for 2000s too. Thanks!

 

[sofiachr]

Hi again,
The second formula for the dubble numbers is not working. I have changed to 4 figures in the year so I have adapted the formula into this:
=IF(DATE(LEFT(A1,4)+0,MID(A1,5,2)+0,MID(A1,7,2)+0)>(TODAY()-(55*365.25)),IF(LEN(A1)>20,IF(DATE(MID(A1,17,4)+0,MID(A1,21,2)+0,MID(A1,23,2)+0)>(TODAY()-(55*365.25)),TRUE,),TRUE),)

Sincerely
Sofia

 

[Gerald Higgins]

That formula returns TRUE for me with an entry like this . . .

19900101-abcd / 19800101-wxyz

 

[sofiachr]

I missed the swedish translation at one place in the formula. Now it works perfectly, thanks again!