Extracting text

[zack_]

Hi Everyone,

It took me awhile but I solved my issue. That said I wanted to know if there is a less convoluted formula to achieve the same result... I am trying to extract the number portion of a text string in order to convert it from text to a number which I will use to compare to the total useful life of that asset.

I used the datedif() function to figure out the difference between two dates and the results of the formula were returned in the below format:

1 years 4 months

1 years 4 months

1 years 5 months

10 years 6 months

<colgroup><col></colgroup><tbody>
</tbody>

I recognized that the years would always be either the first or second value and the months would always be either the ninth or tenth value depending on how many years there were.

To extract values I wanted I used the below formula:

=VALUE(IF(MID(S8,3,5) = "years",LEFT(S8,1)& "." &MID(S8,9,2),IF(MID(S8,4,5) = "years",(LEFT(S8,2)& "." &MID(S8,10,2)),TRIM("."& MID(LEFT(S8,9),1,2)))))

Is there a shorter formula I could use to achieve the same result? Thanks for the input.

 

[Rick Rothstein]

Hi Rick,

Thanks for taking the time to think this through... You are right, my formula is yielding unexpected results...

1/1/2014	3/30/2014		0.167
1/30/2014	3/30/2014		0.167
1/31/2014	3/30/2014		0.083
4/30/2014	3/1/2015		0.833
5/1/2014	3/1/2015		0.833

<tbody>
</tbody>

I would expect that the January dates would all be .167, and I would also expect that the dates in April and May would differ.

I assume this has something to do with how the formula is counting the days... but that is just a guess, and something I need to research a little more. Below is an example from my spreadsheet and what I am trying to achieve

Date Installed Date Disposed Used Life Used Life (Number) Useful Life Fully Depreciated
11/11/2009 12/14/2010 1 years 1 months 1.08 7 No

The formula to get Used Life:
=IF(DATEDIF($O47,$P47,"y") = 0, DATEDIF($O47,$P47,"ym")& " months",DATEDIF($O47,$P47,"y")& " years "&DATEDIF($O47,$P47,"ym")& " months ")

Used Life:
=VALUE(TEXT(DATEDIF(O47,P47,"y")+(DATEDIF(O47,P47,"ym")/12),"#.000"))

Basically I am trying to display the data for used life so that its easy to interpret for anyone that will see the spread sheet. I converted used life from text to a number so that it could be used in calculations (I haven't got actual cost data yet for the servers, so which is why I haven't calculated depreciation)

I am not a "financial guy" (I was a Civil Engineer/Programmer during my working career), so I'll leave the "used life", "depreciation" and what not to you and your colleagues to work out, but as for the date calculation part, it looks like you want to work in whole months (no matter what the date within those months are). If so, let's if a modified version of my last posted formula can work for you...

=LEFT(S8,FIND(" ",S8)-1)+MID(S8,FIND(" ",S8,FIND(" ",S8)+1)+1,2)/12

To do calculation, I would simply work in total months. To convert the value produced by the above formula in such calculations, just multiply its value by 12. If you then want to calculate years and months from it...

=INT(Value/12)&" years "&Value-INT(Value/12)&" months"

where Value is what result you got from manipulating your value in whole months.