• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Date

J

Jagadeeshbs

Member
Hi all,


I have a data in which the date is showing as below. How can i convert it into the DD-mmmm-yyyy(15-Jan-11)format ..?

Please help thanks .


30/08/11

30/08/11

30/08/11

30/08/11

30/08/11

22/09/11

22/09/11

22/09/11

22/09/11

22/09/11

22/08/11

5/9/2011

9/9/2011

25/08/11

25/08/11

25/08/11

25/08/11

25/08/11

25/08/11

25/08/11

25/08/11

30/08/11

30/08/11

30/08/11

31/08/11

31/08/11

31/08/11

31/08/11

31/08/11

31/08/11

31/08/11

1/9/2011

1/9/2011

2/9/2011

2/9/2011

2/9/2011

2/9/2011

2/9/2011

2/9/2011

2/9/2011

2/9/2011

2/9/2011

2/9/2011

2/9/2011

2/9/2011

6/9/2011

6/9/2011

8/9/2011

8/9/2011

8/9/2011

8/9/2011

13/09/11

13/09/11

13/09/11

13/09/11

13/09/11

13/09/11

13/09/11

13/09/11

15/09/11

15/09/11

15/09/11

15/09/11

15/09/11

15/09/11

15/09/11

15/09/11

15/09/11

15/09/11

15/09/11

15/09/11

16/09/11

16/09/11

16/09/11

16/09/11

20/09/11

20/09/11

20/09/11

20/09/11

20/09/11

23/09/11

23/09/11

23/09/11

23/09/11

23/09/11

23/09/11

27/09/11

27/09/11

27/09/11

27/09/11

27/09/11

27/09/11

27/09/11

27/09/11

27/09/11

27/09/11

27/09/11

25/08/11

25/08/11

25/08/11

25/08/11

25/08/11

25/08/11

25/08/11

25/08/11

25/08/11

25/08/11

25/08/11

25/08/11

30/08/11

30/08/11

30/08/11

31/08/11

31/08/11

31/08/11

1/9/2011

1/9/2011

2/9/2011

2/9/2011

2/9/2011

2/9/2011

2/9/2011

2/9/2011

6/9/2011

6/9/2011

6/9/2011

6/9/2011

8/9/2011

8/9/2011

8/9/2011

8/9/2011

9/9/2011

9/9/2011

9/9/2011

9/9/2011

9/9/2011

9/9/2011

9/9/2011

13/09/11

13/09/11

13/09/11

13/09/11

13/09/11

13/09/11

15/09/11

15/09/11

15/09/11

15/09/11

16/09/11

16/09/11

20/09/11

20/09/11

20/09/11

23/09/11

23/09/11

23/09/11

23/09/11

23/09/11

23/09/11

23/09/11

23/09/11

27/09/11

27/09/11

27/09/11

27/09/11

27/09/11

27/09/11

23/09/11

25/08/11

25/08/11

25/08/11

30/08/11

30/08/11

30/08/11

5/9/2011

6/9/2011

6/9/2011

9/9/2011

13/09/11

15/09/11

15/09/11

16/09/11

22/09/11

22/09/11

22/09/11

23/09/11

23/09/11

31/08/11

22/08/11

22/08/11

22/08/11

1/9/2011

24/08/11

24/08/11

30/08/11

6/9/2011

13/09/11

20/09/11
 
Jagadeeshbs

Select the Data

Ctrl 1

Number Tab, Custom

Enter dd-mmm-yy in the Type box:

Apply
 
Hi

The reason y it worked for a few & did not work for the rest is because the date is in the begging, it is followed by month & year.

I know the work around & not a straight solution. You can try this.


Extract these in separate columns

First extract the month =MID(K1,4,2)

Next the date =LEFT(K1,2)

Lastly the Year =RIGHT(K1,2)


Now Concatenate =CONCATENATE(L1,"/",M1,"/",N1)


Copy & special Paste to the next columns, the error will be highlighted with a small green flap, click on that & click on Convert XX to 20XX & your date is formatted the way u want
 
I like srinidhi's idea, but I don't think it will work on all the dates since some have 2 digit days and/or months, and others have 1 digit.


It might be easiest to use Text-to-Columns, set the "/" as your delimiter, and then put it all together using:

=DATE(C2,B2,A1)
 
Hello srinidhi,


Thanks for offering the help, but this is not working when the date mentioned is as 5/9/2011. But it works very well when the date is mentioned 22/09/11.


any help with this regard will be much appreciated..

Thanks
 
Hey Jagadeeshbs,


As you dates are in different format, you have to use both mine & Hui's formula.

I guess Luke has simplified things for u, Thanks Luke it is great learning for me.
 
I'm suspecting some of this data is text


Select the data

Text to Columns

Next

Next

Date DMY

Finish


Then try my First Solution
 
Jagadeesh....


I was in the middle of writing a very complex formula to get this....and was about to finish... when I changed my Regional Settings to "English (United Kingdom)".


Try this out and hopefully your issue will be resolved... I will continue to write that formula and share as nearing completion and do not want to give up now...


~VijaySharma
 
And then you can convert the format to DD-MMM-YYYY for the whole column...


Here is the formula..


=IF(ISNUMBER(A1),DATE(IF(MID(A1,LEN(RIGHT(A1,3)),1)="/",RIGHT(A1,2),RIGHT(TEXT(A1,"yyyy"),4)),IF(MID(A1,LEN(LEFT(A1,2)),1)="/",LEFT(A1,1),LEFT(TEXT(A1,"dd"),2)),IF(ISNUMBER(A1),MID(TEXT(A1,"dd/mm/yyyy"),SEARCH("/",TEXT(A1,"dd/mm/yyyy"),1)+1,2),MID(A1,SEARCH("/",A1,1)+1,2))),DATE(IF(MID(A1,LEN(RIGHT(A1,3)),1)="/",RIGHT(A1,2),RIGHT(TEXT(A1,"yyyy"),4)),IF(ISNUMBER(A1),MID(TEXT(A1,"dd/mm/yyyy"),SEARCH("/",TEXT(A1,"dd/mm/yyyy"),1)+1,2),MID(A1,SEARCH("/",A1,1)+1,2)),IF(MID(A1,LEN(LEFT(A1,2)),1)="/",LEFT(A1,1),LEFT(TEXT(A1,"dd"),2))))


There is one catch associated..... for all cells which only have the 2 digit year (as 11) there is a complete possibility of the year getting formatted as 1911....


for all 4 digit years.... it will be allright...


However as per your requirement to display "DD-MMM-YY" format this would suffice...


Just do not use the date for any calculation.... someone may turn out be 100+ years old and yet working :)


HTH

~VijaySharma
 
Excel uses your Windows/Mac regional settings to determine how to interpret dates when they are entered/imported/converted. The default regional settings in regards to two digit years have a 21st century cutoff of 2029. Two digit years 00-29 become 2000-2029, 30-99 become 1930-1999.
 
You must log in or register to reply here.
Back
Top