Text to Date Format

[AsifSayed]

Dear All,

I have attached excel, where date is mention in B Coloum but when I am formatting it, it is not showing in proper date format. I want format in eg 18-Mar-2013

[judgeh59]

The dates in column B are not actual dates but text...the dates on Sheet1 column B are real dates and can be formatted the way you want by highlighting the entire column, right click and go to formatting....click on custom and enter dd-mmm-yyyy....and that should give you your format....to do that on Sheet 2 you'll have to convert the text to an actual date....HTH

[AsifSayed]

Hi,

Can you attach my excel file and resend the way you are suggesting in 03 December 2013 format.

Thanks for you help!

[judgeh59]

You have mixed dates on Sheet 2 Col "B"....some are text shown as dates and some are real dates...I created a formula that checks and then converts it to your format....HTH

[AsifSayed]

HI,

Thanks for you help, can you do it in sheet 1.

[judgeh59]

no problem...the formula in Sheet 2 works in Sheet 1 if you change A2 to B2 and copy it down....see new upload

[Kevin UK]

Hi AsifSayed

Select Column B > Data tab > Text to Columns > Delimited > Next > Next > Tick the Date under Column data format & select MDY from the drop down box > Finish. Do the same for column C.

[AsifSayed]

Hi,

Kindly see the attached screenshot. I am not getting all dates in correct format.

Thanks for you precious time.Screeshot1.JPG

[judgeh59]

which column are you clicking on?....what does the actual column look like?....is it correct?....autofilter may not look at the formatting.....here are a couple of things to remember....actual dates and times are really numbers.....like 41355 is the numeric representation of today....time is a fraction of that....so .25 is really 6AM and .5 is noon....now, formatting only changes HOW it looks, it does NOT change the actual data....so to determine if it is TEXT or DATE you can do a couple of things....first, right click on the cell and go to formatting and click on General, if it comes up a number IE:41355 then it is a real Date....otherwise it's TEXT.....so, it is possible that the autofilter ignores formatting....

[AsifSayed]

Ok, in my excel some are in date format and some are looking like date but they are text, so I want to convert that text in to actual date format. Is it possible

[judgeh59]

yes, Kevin's solution for converting will work in both sheets....then you can format the real Dates using custom formatting like showed in Post #2 of this thread....

[AsifSayed]

I have tried but not working, can you send again that excel file.

Thanks a lot for you effort it is highly appreciated

[judgeh59]

I used Kevin's system to convert all date columns to real dates...then used custom formatting to change it to your format....you really don't even need the formulas I created....

[AsifSayed]

I have tried but not working, can you send again that excel file.

Thanks a lot for you effort it is highly appreciated

[judgeh59]

still not working?....or did the system generate the exact same post....#12 and #14 are exactly the same....

[AsifSayed]

I am trying but not getting

I have done text to columns as informed by kevin and custom formating also.

Can you explain briefly. Thanks a alot. The file which you have send it perfectly as which I want.

[AsifSayed]

Hi Ernest,

I have tried text to column as informed by Kevin, and custom format also. The file which you have send is perfect which I want. Can you please give screenshot if possible. Thank Thanks a lot

[judgeh59]

here is a word doc that should walk you through it....

[Kevin UK]

@ judgeh59

Nice, keep up the good work!

[judgeh59]

@ Kevin Thanks....and thanks for the text to Column heads up....I like it....

[AsifSayed]

Hi,

I have tried the same earlier and now also but it is not working. Can you take my remote desktop and try it on this Sunday.

[judgeh59]

Sunday's don't work for me.....and it's against my companies policy to do remote desktops outside our networks....explain "not working"....it's not converting to a real date? or it's not changing the format to dd-mmm-yyyy?....

[AsifSayed]

Oops!

its not converting to a real date.

[AsifSayed]

Oops!

its not converting to a real date.

[judgeh59]

please upload the file after your have tried to convert it using Text to Column.....we'll figure this out.....