Dates problem - text to numbers

[shendik]

So I needed to extract the "062711" part of the following string:
B1 = "110623 Manufacturing Detail_CMO_MASTER_062711.xlsm"
So I used the following combination of text functions in a different cell:
"(MID(Sheet1!B1,LEN(Sheet1!B1)-10,6)"
and this gave me the "062711" that I was looking for.

I understand that while this looks like a number, but excel considers it a text. So I used the "value" function to convert this text to a number:
"VALUE((MID(Sheet1!B1,LEN(Sheet1!B1)-10,6))"

The problem that I am having is that when I format this number "062711" as a date, it is showing as September 10, 2071 instead of July 27, 2011. This is ofcourse not what I want.

Is there an elegant solution by which I can essentially tell excel to consider the first 2 digits as month, next 2 as date, and the following two as year? Since I have large amount of texts such as the one shown above, if there is a code etc. that I can pop into a macro, it would be very helpful.

I realize that is quite a common problem with Excel but am still having trouble with it. Please share your ideas on how to deal with this. Thanks!

 

[Joe4]

Excel stores Dates as number, specifically the number of days since 1/1/1900. So if you convert a number to a date, that is the logic it uses (if you change any date to General format, you will see how it is stored).

Try using the Date(year,month,day) function instead, i.e.

=DATE("20" & MID(Sheet1!B$1,LEN(Sheet1!B$1)-6,2),MID(Sheet1!B$1,LEN(Sheet1!B$1)-10,2),MID(Sheet1!B$1,LEN(Sheet1!B$1)-8,2))

It could also be made into a User Defined Function also, which can be used directly on the spreadsheet or in a macro.

 

[Joe4]

Here is what that UDF would look like:

Function MyGetDate(myInput As String) As Date
 
    Dim MonthPiece As Integer
    Dim DayPiece As Integer
    Dim YearPiece As Integer
 
    MonthPiece = Mid(myInput, Len(myInput) - 10, 2)
    DayPiece = Mid(myInput, Len(myInput) - 8, 2)
    YearPiece = "20" & Mid(myInput, Len(myInput) - 6, 2)
 
    MyGetDate = DateSerial(YearPiece, MonthPiece, DayPiece)
 
End Function

(of course, you may want to add error handling in case an entry doesn't meet the criteria, or it is run on the wrong cell).

Then you would just use it like any other function. From a spreadsheet, it would look like:
=MyGetDate(B1)

 

[shg]

Another way:

=--TEXT(MID(Sheet1!B1,LEN(Sheet1!B1)-10,6), "00-00-00")

 

[shendik]

Great elegant ways to tackle the problem! Thanks guys!