Not sure where this post should be but it is a format / general Excel question. I was given an excel sheet with one cell labeled "YYMMDD" for the date. The records in that field are, for example, "170603". I am trying to get that text string into a date format so it shows up as 06/03/17 (it is NOT a formatting function.). Manual is out of the question since there are 37000+ records can anyone tell me what the best way to convert this is?
with your example you can try =DATE(20&LEFT(A1,2),MID(A1,3,2),RIGHT(A1,2))
you will need VBA to change to a date in the cell
in a new column you could use
if the dates are in column A startign row 2
=DATEVALUE(RIGHT(A2,2)&"/"&MID(A2,3,2)&"/"&LEFT(A2,2))
and copy down
that will change to a date
then you could click on the column and copy
paste special back over the original column
Wayne
if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here
If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
also you can use Text2Columns with YMD and after all it will be adapted to your local settings
Awesome...this worked perfectly and is exactly what i needed. Thank you sandy (no caps)Originally Posted by sandy666
hehehe , thanks for no caps
If that takes care of your original question, & to say Thanks, please click on Add Reputtion (bottom left corner of the post of the person(s) who helped you)
then select Thread Tools from the menu (top right corner of your thread) and mark this thread as SOLVED.
If you did it - ignore it.
Thank you.
try this
Formula:
Please Login or Register to view this content.
v A B 1 170603 6/3/2017
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks