Problem arises while changing date format

[sam143sta]

Hi,

While working in with data, mostly I face one problem in changing date format.

For Ex:

I have columns with this date format :i.e.....

Saturday, April 09, 1955

and i want it in 04/09/1955 i dont know why its not converting .... can u please help me out for the same.

Thanks & Regards,
Sam.

 

[villy]

Check the cell formatting...Ctrl + 1

 

[sam143sta]

Hi Villy,

I have already tried this but its not working.... can u tell me any alternate of that...

Thxs & Regards,
Sam

 

[villy]

Did you checked if there is any macro that is running automatically converts those columns?
Otherwise, Check also the setting in Control Panel|Language might be set up in that format...

 

[RoryA]

Your data is text, not an actual date, which is why changing the format does not work. You would need formulas or code to alter the data first - what regional settings are you using - US?

 

[R J Solanki]

I was also faced this sort of problem but finally i fix it Try this
Go to Start then control panel then Regional & language option then
Regional option then customize then hit date tab in which three things is there (1) Calendra (2)Short date (3)long date go to short date in which short date format write dd/mm/yyyy & then ok

 

[sam143sta]

Hi Rorya,

You are right its in text format... can you please tell me how to convert this text format data in date format.... is there any formula or coding to change this format...

Thanks & Regards
Sam

 

[Yahya]

may be this way will help

Excel Workbook
	A	B	C	D	E
1	Saturday, April 09, 1955		April 09, 1955		09/04/1955
Sheet2

Excel 2007

Cell Formulas
Range	Formula
C1	=TRIM(REPLACE(A1,1,FIND(",",A1),""))
E1	=TEXT(DATEVALUE(MID(C1,FIND(" ",C1)+1,FIND(",",C1)-FIND(" ",C1)-1)&"/"&LEFT(C1,FIND(" ",C1)-1)&"/"&RIGHT(C1,4)),"dd/mm/yyyy")