Date Format Problem

[kryptonian]

I have a report that generates a date in this format - 12/6/2008. In excel, this is interpreted as December 6, 2008 in month/day/year format.

That's my problem. It shouldn't be interpreted as such and should be interpreted as June 12, 2008. The report comes from a client so that rules out changing the date format from the report source.

I've been tinkering with using LEFT/MID/LEN combinations but I have only managed to extract the first part of the date, which in this example is 12. The formulas I used were:

=LEFT(TEXT(A1,"m/d/yyyy"),FIND("/",TEXT(A1,"m/d/yyyy"))-1) and
=MID(TEXT(A1,"m/d/yyyy"),1,FIND("/",TEXT(A1,"m/d/yyyy"))-1)

How can I extract the text between the two "/"?

I am planning to just use concatenate afterwards to re-arrange the numbers extracted from this cell.

If someone has a better way to go around this problem, my thanks in advance!

 

[Jonmo1]

Try

=DATE(YEAR(A1),DAY(A1),MONTH(A1))

 

[Jonmo1]

Or actually, even better. Since 20/6/2008 is not a valid date in MM/DD/YY format, it would be interpraited as text not a date, so the Date function I suggested before won't work for that...

Instead, try Data - Text to Columns - Deliminated
On the last step, choose Date DMY

 

[kryptonian]

I needed a formula that is flexible enough to get the data that I want without having to determine if it is a valid date format or not, i.e., 30/6/2008 is not a valid date format since there is no month which will correspond to 30.

Anyway, my brain already went back from sleep mode and I managed to come up with this formula:

=CONCATENATE(MID(TEXT(A6,"m/d/yyyy"),FIND("/",TEXT(A6,"m/d/yyyy"))+1,FIND("/",TEXT(A6,"m/d/yyyy"),FIND("/",TEXT(A6,"m/d/yyyy"))+1)-FIND("/",TEXT(A6,"m/d/yyyy"))-1),"/",MID(TEXT(A6,"m/d/yyyy"),1,FIND("/",TEXT(A6,"m/d/yyyy"))-1),"/",MID(TEXT(A6,"m/d/yyyy"),FIND("/",TEXT(A6,"m/d/yyyy"),FIND("/",TEXT(A6,"m/d/yyyy"))+1)+1,LEN(TEXT(A6,"m/d/yyyy"))-FIND("/",TEXT(A6,"m/d/yyyy"),FIND("/",TEXT(A6,"m/d/yyyy")))))

The formula will get 30, 6, and 2008 from 30/6/2008 then re-arrange it to this value: 6/30/2008.

 

[Jonmo1]

Did you try the Text To Columns?

 

[jaznit]

Did you try the Text To Columns?

Kryptonian: the Text to Columns works perfect. I have a samilar problem.