Convert Age to Number format not working

[BradleyS]

I have a formula to get the age in years and months which works great =DATEDIF($B2,$C2,"y")&"."&DATEDIF($B2,$C2,"ym"), but I need to convert it into a number, so that I can use it in an autofilter.
However, when I try to convert it to a number format, I just can't get it in the correct format.
The 3.10 shows as 3.1 and so does the actual 3.1 value, so one cannot be distinguished by the other. I ideally need the numbers to show as listed under the 1st column.
I have tried TextToColumns, PasteSpecial Multiply by 1, VBA .Value = .Value

If you add an autofilter to the table and use the last column which has been converted to a number and try to filter ages <=3.4, you will see that it includes 3.10 and 3.11, which I do not want.
Does anyone have a resolve to my predicament?

Required	Date of Birth	Today	Calc Age	Converted
3.12	05/11/2020	05/11/2024	4.0	4
3.11	05/12/2020	05/11/2024	3.11	3.11
3.1	05/01/2021	05/11/2024	3.10	3.1
3.09	05/02/2021	05/11/2024	3.9	3.9
3.08	05/03/2021	05/11/2024	3.8	3.8
3.07	05/04/2021	05/11/2024	3.7	3.7
3.06	05/05/2021	05/11/2024	3.6	3.6
3.05	05/06/2021	05/11/2024	3.5	3.5
3.04	05/07/2021	05/11/2024	3.4	3.4
3.03	05/08/2021	05/11/2024	3.3	3.3
3.02	05/09/2021	05/11/2024	3.2	3.2
3.01	05/10/2021	05/11/2024	3.1	3.1
3	05/11/2021	05/11/2024	3.0	3

 

[StephenCrump]

Why not go straight to numeric with =DATEDIF(B5,C5,"m")/12

(Although there are some known calculation issues with DATEDIF, so it may not produce equivalent results in all cases)

 

[Alex Blakenburg]

We can't recommend what you are trying to do since it will look like a decimal value but actually be y.mm but I think this will get you what you want.
(Stephen is showing the actual value in years as a real decimal value.)
Note:
• You have 3 as showing 3 but 4 showing as 3.12. That's not going to work so I have it showing as 4.
• I have formatted the column as Number with 2 Decimal places.

Book1
	A	B	C	D	E	F
1	Result y.mm	Date of Birth	Today	Calc Age	Converted	OP Required
2	4.00	5/11/2020	5/11/2024	4	4	3.12
3	3.11	5/12/2020	5/11/2024	3.11	3.11	3.11
4	3.10	5/01/2021	5/11/2024	3.1	3.1	3.1
5	3.09	5/02/2021	5/11/2024	3.9	3.9	3.09
6	3.08	5/03/2021	5/11/2024	3.8	3.8	3.08
7	3.07	5/04/2021	5/11/2024	3.7	3.7	3.07
8	3.06	5/05/2021	5/11/2024	3.6	3.6	3.06
9	3.05	5/06/2021	5/11/2024	3.5	3.5	3.05
10	3.04	5/07/2021	5/11/2024	3.4	3.4	3.04
11	3.03	5/08/2021	5/11/2024	3.3	3.3	3.03
12	3.02	5/09/2021	5/11/2024	3.2	3.2	3.02
13	3.01	5/10/2021	5/11/2024	3.1	3.1	3.01
14	3.00	5/11/2021	5/11/2024	3	3	3
Sheet1

Cell Formulas
Range	Formula
A2:A14	A2	=VALUE(DATEDIF($B2,$C2,"y")&"."&TEXT(DATEDIF($B2,$C2,"ym"),"00"))

 

[BradleyS]

Thank you, but neither really worked correctly. In the end I just removed the decimal that separated the y and m and it worked fine, as a whole number for the autofilter.

 

[Alex Blakenburg]

Do you want to show us ? Wouldn't that give you 31, 32, 33, ..... 310, 311, 40

 

[BradleyS]

Do you want to show us ? Wouldn't that give you 31, 32, 33, ..... 310, 311, 40

Yes. Exactly that

 

[Phuoc]

Thank you, but neither really worked correctly. In the end I just removed the decimal that separated the y and m and it worked fine, as a whole number for the autofilter.

Another way:

=DATEDIF($B2,$C2,"y")*100+DATEDIF($B2,$C2,"ym")

 

[BradleyS]

Another way:

=DATEDIF($B2,$C2,"y")*100+DATEDIF($B2,$C2,"ym")

This worked great! as i had a few other age ranges to deal with, which didn't work with my method, but this has done the trick.
Thank you