Hi there,
I have a column that displays a date combination something like dd.mm.yyyy / dd.mm.yyyy. I managed to separate them into two columns by doing a left(A,10) and right(A,10) however the dates are now presented as dd.mm.yyyy, does anyone know of possible ways to change it to mm/dd/yyyy instead?
I have tried datevalue(a1) and TEXT(A1,"DD-MMM-YYYY") to no avail.
Appreciate your help.
Thanks in advance.
=datevalue(mid(a1,find(".",a1)+1,2)&"/"&left(a1,2)&"/"&right(a1,4))
Ben Van Johnson
hi treeantz. here are 2 methods you can try.
1. Text to column
i) select data in column A (eg. A1:A10)
ii) go to Data -> Text to Columns -> Delimited -> Next -> Next -> Date: DMY -> Destination: $A$1 (to replace existing. choose somewhere else if you dont wish to replace). -> Finish
2. Formula (provided it's always 2 digits for day & month)
=DATE(RIGHT(A1,4),MID(A1,FIND(".",A1)+1,2),LEFT(A1,2))
Thanks, if you have clicked on the * and added our rep.
If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".
"Contentment is not the fulfillment of what you want, but the realization of what you already have."
Tips & Tutorials I Compiled | How to Get Quick & Good Answers
Hi
Have you tried format cells select from the pulldown which country you want and then choosing date format, your dates should then change automatically to the selected format.
Chris
Click my star if I helped Thanks
you could try this, and then format as needed...
=VALUE(SUBSTITUTE(LEFT(A1,10),".","/"))
=VALUE(SUBSTITUTE(right(A1,10),".","/"))
edit: how cool is that? 3 different solutions to the same question...excel rocks!!!!
lol
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
woah, all works well. Thank you very much for all your support.
This thread is now solved. Again, many thanks
we are happy to helpconsider thanking those who helped, by clicking the star on the lower left of the post
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks