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

 

[dreid1011]

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

Why would 2 months convert to .5 and not .17? Maybe provide full detail about how the conversion should work.

 

[meppwc]

Yes, you are correct. I made a mistake.

 

[Joe4]

Will the format ALWAYS be like this:
"X Years and Y Months"
even if the "X" or "Y" part were to be zero?

If there are any other formats, please show examples of all the different possible structures.

 

[meppwc]

Correct

 

[alansidman]

One way with Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Split Column by Delimiter",{"Column1.1", "Column1.4"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Column1.1", Int64.Type}, {"Column1.4", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [Column1.4]/12),
    #"Inserted Addition" = Table.AddColumn(#"Added Custom", "Addition", each [Column1.1] + [Custom], type number),
    #"Removed Other Columns1" = Table.SelectColumns(#"Inserted Addition",{"Addition"})
in
    #"Removed Other Columns1"

 

[meppwc]

Is there a way to do it with a formula?

 

[alansidman]

Maybe this: =TRIM(MID(" 0 Year " & A2,MAX(IFERROR(FIND("}}}",SUBSTITUTE(" 0 year " & A2,"year","}}}",{1,2})),0))-3,2))+TRIM(MID(" " & A2 & " 0 month",MIN(IFERROR(FIND("}}}",SUBSTITUTE(" " & A2 & " 0 month","month","}}}",{1,2})),999))-3,2))/12

found at: Find, then convert the year and month to decimal

 

[meppwc]

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

 

[Rick Rothstein]

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.