LinkBack URL
About LinkBacks
I'm trying to change the format of dates in one column, but not all the cells in that column responds to the change.
any help is appreciated
thanks
I'm trying to change the format of dates in one column, but not all the cells in that column responds to the change.
any help is appreciated
thanks
Try selecting one section of dates that aren't formatting within the column...
Go to Data|Text to Columns... then skip to the 3rd window and select Date and DMY in the column data format area.. click Finish.
repeat for each unformatted section..
Last edited by NBVC; 01-20-2010 at 05:46 PM.
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
i tried that, didnt work...the dates in the cells are in the format dd/mm/yy, unless i re-type it in the format yy/mm/dd, it wont change. i attached the file so feel free to take a look
I did test it with your file...
Remember to select only the first range of cells up to the point where the dates are okay, then in the 3rd dialogue, you have to choose DMY beside the Date option in the Column Data Format area....
How about =DATE(RIGHT(A1,4),MID(A1,FIND("/",A1)+1,LEN(A1)-5-FIND("/",A1)),LEFT(A1,FIND("/",A1)-1)) then copy->paste special as values over itself and delete A.
You'll have to start over because it won't work on those that Excel has already changed, but it changed them to the wrong dates anyway.
Another option is to change your regional settings so that the date is read in the European way.
*Edit: tested with NBVC's approach and it works. Go with his.
Worked fine for me following NBVC's steps. See attached.
Remember what the dormouse said
Feed your head
ok now it works, thanks nvbc!!
Must have been the moon cycle or improper alignment of the planets.ok now it works,
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks