How to change Date stored as text to number format

[Akash kothari]

Hello friend

In the attached file as you can see the dates under column A1 to A5.
A4 & A5 are in number format but A1 to A3 are not.
I have tried using "0" and paste special by adding it.
=value formula
applying the Format.

can anyone suggets me how to change.

Akash

 

[Sathish KV]

Hi Akash,

Select the date formated as text
Hit Alt+D, E (Text to Columns Wizard)
Hit NEXT twice
In the screen select radio button against 'Date' and in the dropdown select 'MDY'

Hope this solves your problem!

 

[Akash kothari]

Hi Satish,
Thank for your response
I have already tried this before but its not working. (Only cell A4 & A5 are getting converted into dates/number as yyyy/mm/dd

I have also tried =value formula but its giving "Value Error".

Thanks Akash

 

[vijay.vizzu]

Dear Akash

I tried above procedure (text to columns) in your excel file.. it works correctly.. please check it again.

And if you applied same procedure, but still shows data as it is, please select the cell one by one and press F2 key. Is the cell values getting changed or not ??

 

[Bhawani]

@Akash kothari
1. Select Column A
2. Go To Ribbon
3. Data
4. Text to column
5. Select Delimited
6. Press Next
7. Press Next
8. Select Date
9. Select 2nd option from date drop down list***
10. Click Finish

*** If you found it is not working try choosing 3rd option from date drop down list***

Regards,
Bhawani

 

[Akash kothari]

Thank all,

It worked,

Akash