Date Format Question

[dariushou]

I have the text ouput of "Jun08-Mar38" in a cell--let's say B59. I want to convert it to the format 6/08 - 3/38 using a formula or vba, but i can't figure out how to do that. There are many dates that i have that i need converting and there is no pattern to the months/years.

Thanks!!

 

[iliace]

This assumes month is always three letters and year is always two digits.

=TEXT(DATEVALUE(LEFT(LEFT(B59,FIND("-",B59)-1),3)&" "&RIGHT(LEFT(B59,FIND("-",B59)-1),2)),"mm/yy")&" - "&TEXT(DATEVALUE(LEFT(RIGHT(B59,LEN(B59)-FIND("-",B59)),3)&" "&RIGHT(RIGHT(B59,LEN(B59)-FIND("-",B59)),2)),"mm/yy")

 

[dariushou]

Great!! That worked perfect. Would you happen to know how i could perform the calc as a macro and then copy the result into a worksheet of another book. For example, I currently have a macro that opens up workbook "01B.xls" which is where the date resides. I would like the macro to perform your calc on cell B59 in the worksheet titled "01B PY" in the related workbook "01B.xls" and then paste the result in another workbook titled "Master.xls" in worksheet "01B" cell A1.

 

[dariushou]

The formula you gave works great when the 1st month begins in 2008. But i can't get it to work when the text is say Jun18-Mar38. The result i want is 06/18 - 03/38. The formula you gave gives, 06/08 - 03/38.

Any ideas?

 

[barry houdini]

If data is in A1 try

=IF(A1="","",TEXT(1&LEFT(A1,5),"mm/yy")&TEXT(1&RIGHT(A1,5)," - mm/yy"))

 

[dariushou]

Thanks Barry. That worked perfect.

Would you happen to know how to do the following:

Perform the calc as a macro and then copy the result into a worksheet of another book. For example, I currently have a macro that opens up workbook "01B.xls" which is where the date resides. I would like the macro to perform your calc on cell B59 in the worksheet titled "01B PY" in the related workbook "01B.xls" and then paste the result in another workbook titled "Master.xls" in worksheet "01B" cell A1.