Text to Date Formula?

[bluartisteyes]

I can't get dates to convert automatically anymore when copied from tv dot com and it's driving me crazy because I don't have the time it takes to change fix each one individually, I use excel to keep my place on every tv show I watch so I don't forget where I'm at in series on dvd or on tv and also helps me not to watch out of order by mistake which also drives me crazy since the writer intended it to be watched in order thus why it's called a series and not separate unrelated episodes like movies or shorts.

Can someone please tell me is there a formula that can fix this issue, I am pretty sure it's tv dot com's website that changed something with format and made it start copying as text only, but I could be wrong I also changed to the more correct date format for computer sorting yyyy-mm-dd usa even though really the setting is uk date format, I have it set to USA because I changed it in my language settings area so I don't have to change it for every excel file. So it could be that causing problem, but I don't want to go back to the bad un-sort-able USA default of mm/dd/yyyy I hate that settings it's not computer friendly and it's beyond me why anyone would want to use it.

If anyone has a solution for this I would appreciate it I have excel 2007 and it won't convert it either something isn't working right or it can't convert this type of text input from that site, I already tried the obvious right click format type text changed to date and still nothing changes, it doesn't understand the format it's getting and can't convert it to a date (I'm guessing by the way it acts). I search net and all I could find that looked promising is an add-on that the guy is selling like a yearly membership subscription at some outrageous price that I could never afford and I would never want to throw away money on a non-permanent purchase anyway if I buy an add-on I want to own it even if I never get a software update for it, I do not believe in paying for it every month or year or whatever. I just see that as a major rip off.

 

[bluartisteyes]

Sry to site admin or whoever I did not and was not trying to post duplicates your site was taking an unreasonable amount of time to simply post and I reloaded the page.

 

[VoG]

Try selecting the dates, Data > Text to Columns, click Next twice, tick Date and select the date format (like DMY) from the drop down, then click Finish.

 

[bluartisteyes]

I got really strange results that did change a few of them to correct date format, but most it didn't effect at all.

 

[VoG]

Can you give some examples of the text dates that you have, and what they should be converted to.

 

[bluartisteyes]

Oh ok it does work if I do it before I paste it into my destination excel like if I copy everything including macros into a new excel sheet and apply that it works perfect thank u so much this will save about years of my time lol. I guess before when I tried it something was different with the way I always paste using paste special values only and so for some reason it won't work after I paste into my file but I have a temp new excel up anyway to copy from when I compile my worksheets so this will work perfect.

 

[bluartisteyes]

Can you give some examples of the text dates that you have, and what they should be converted to.

Its just different episodes lists from tv archive site tv dot com. But it does work as long as I apply it in temp new excel file, so thank u. I appreciate the help and the loads of time that is going to save me.

 

[bluartisteyes]

Lol this was before I found trakt TV and I no longer even use excel for this.