To convert text string to date format

[rinka]

Hi I have to format the following excel from text to date format. But some it is not getting coveretd. pls help. Need it urgent. It is downloaded from SAP entry.


DOJ

14.01.2010

21.02.2011

21.02.2011

21.02.2011

17.02.2011

17.02.2011

24.02.2011

21.02.2011

23.02.2011

24.02.2011

18.02.2011

28.02.2011

28.02.2011

21.02.2011

03.03.2011

02.03.2011

 

[fred]

crtl F

replace

find "."

replace all with "/"

 

[Luke M]

=DATE(RIGHT(A2,4),MID(A2,4,2),LEFT(A2,2))

 

[Luke M]

Fred,

Find and replace might work, but only if the native language and time setting uses a dd/mm/format. Otherwise, the 02.03.2011 date will get changed to Feb 3rd, not March 2nd.

 

[fred]

Wouldn't the local version of Excel recognize it??

 

[Luke M]

Depends. Assuming local setting uses default mm/dd/yyyy, XL will "know" that 14.02.2011 changed to 14/02/2011 should be February 14, because there isn't a 14th month. However, XL doesn't use any type of "pattern recognition/similarity" analysis, so it simply goes through the list of dates, swapping the month and day around IF it sees the month is impossibly high. Otherwise, it'll just assume the date is already in default format.

 

[fred]

Thanks.

 

[rogerimee]

Hi,


You can use the the text to columns application (from DATA ribbon). First highlight the data and select the delimited then click next twice then select date and select DMY then OK. Hope this will help...

 

[Luke M]

Rogerimee,

That sounds like a nice feature. Something I'll have to look forward to when I finally upgrade to 2007.

 

[rinka]

Thanks guys the delimit funtion worked. Fabulous

 

[rinka]

Guys, This is quite typical. The format below will not convert in either number or date Tried delimit not working. pls help.

11th Aug 2011

5th Aug 2011

5th Aug 2011

19th Aug 2011

12th Aug 2011

16th Aug 2011

8th Aug 2011

11th Aug 2011

19th Aug 2011

23rd Aug 2011

25th Aug 2011

29th Aug 2011

26th Aug 2011

24th Aug 2011

 

[Luke M]

I'm assuming this is a new type of problem, as the format is very different than your original post. Would a formula work for you?

=DATEVALUE(REPLACE(A2,FIND(" ",A2)-2,3," "))

 

[carlosgcastroi]

@ Rogerimee (and everyone else) - The text to columns application (from DATA ribbon) works great and I am able to get month, day and year in three columns (all values). But how do I put together these columns in one single column for the date?


RAW DATA VALUE/TEXT? VALUE TEXT to DATE MONTH DAY YEAR

(MM-DD-YY)

01-12-10 FALSE 01-12-10 01-12-10 1 12 2010

01-12-10 FALSE 01-12-10 01-12-10 1 12 2010

01/13/2010 TRUE #VALUE! 13-04-16 1 13 2010

01/13/2010 TRUE #VALUE! 13-01-10 1 13 2010

 

[NARAYANK991]

Hi Carlos ,


The DATE function does just this ; its syntax is :


=DATE(year,month,day)


Narayan

 

[carlosgcastroi]

never mind... just realized that I can use DATE... I guess I spend too much time using IF, IF ERROR, etc, etc...