Formula For Date Conversion

[meppwc]

I have a column that tells me the age of items. Those entries appear as below

3 Years and 2 Months Is there a formula that will convert this to a number form with 2 decimal points? The above example would be something like 3.50

 

[alansidman]

Should I assume that "A2" in the formula is the cell that contains "3 Years and 2 Months"

Yes

 

[Dave Patton]

Dates Time.xlsm
	A	B
2	3 Years and 2 Months	3.166667
4e

Cell Formulas
Range	Formula
B2	B2	=TEXTBEFORE(A2," ")+TEXTBEFORE(TEXTAFTER(A2," ",3)," ")/12

 

[meppwc]

Assuming your first value is in cell A2 and that your text only contains letters, spaces and numbers (and no other type of character), then put this formula in cell B2, format the cell to display only two decimal places and then copy this formula down to the bottom of your data...

=LET(n,TEXTSPLIT(UPPER(A2),VSTACK(" ",CHAR(SEQUENCE(26,,65))),,1),INDEX(n,1)+INDEX(n,2)/12)

NOTE 1: This formula will handle any text as long as no other numbers are part of it and that there are always a number for the years and a number for the months (years first, months second) even if that number is 0. So, the text could look like this "3 Years and 2 Months" or like this "He was 5 years and 0 months old" and it would work.

NOTE 2: If your text could contain other characters (mainly, punctuation), let me know and I'll modify the formula to handle them.

This works perfectly. I am very grateful to you.