I've got date strings formatted this way: "Tuesday, January 3, 2012"
How can I convert them in a date format in Excel 2010?
Thank you in advance!
Add a comment
|
2 Answers
What works can depend on regional settings. If you have US regional settings this formula will give a date based on your data in A1
=TRIM(REPLACE(A1,1,FIND(",",A1),""))+0
format in required date format
This version should work on a wider range.....
=TRIM(MID(A1,LEN(A1)-7,2)&REPLACE(REPLACE(A1,LEN(A1)-7,3,""),1,FIND(",",A1),""))+0
Try this version which should work for Italian settings - [I assume the data is in English as shown, e.g. months are January, February etc.]
=DATE(RIGHT(A1;4);SEARCH(MID(A1;FIND(",";A1)+2;3);"xxjanfebmaraprmayjunjulaugsepoctnovdec")/3;MID(A1;LEN(A1)-7;2))
4 Comments
Andrea Silvestri
Since I'm using the Italian version, I'm using the second you posted, but I get #VALUE as result. Specifically is the FIND function that returns #VALUE
Andrea Silvestri
Fixed it! The FIND function problem was that I replaced the comma with a semicolon by mistake. The #VALUE problem is caused by the "+0". What's the purpose of that? Thank you for the answer btw
barry houdini
The idea is that the date is converted to a text string like
3 January 2013 [a valid date format] and then the +0 converts that to an actual date - if that isn't working it's probably because January needs to be the Italian equivalent - gennaio? - let me add a formula that should work for you......barry houdini
I added a formula that should work for Italian regional settings
There's the DATEVALUE worksheet function which converts text to dates, however it doesn't seem to accept "Tuesday, January 3, 2012". But it does accept e.g. "3 January 2012" so if you manipulate the string into that format you should be able to get it to work.
Just a thought, these data functions like DATEVALUE often depend on your display settings for date, so what works for me in London UK may be different if you're elsewhere in the world with different display settings.
1 Comment
Andrea Silvestri
Thanks to Barry I can have the date in the "3 January 2012" format, but since I'm using an italian version of excel it doesn't take it as a good date for the DATEVALUE function =\ Is there a way to translate it or let it read foreign dates?