In the attached file the dates are shown separated by dots (full stops if you like). Some dates are clearly number, month and year, whilst others are just month and year.
I would like to see the dates as: day number, month as the first three letters of which ever month, and the year in the usual format.
I have tried Custom format to alter things and that does not seem to want to work.
Can anyone offer a solution.
Relevant file attached.
Dave
If it is an occasional task, use a helper column with this formula in row 4 and copy it down
Then PasteSpecial(Values) the helper column into the Date column.Formula:
Please Login or Register to view this content.
If you have to do this very often, use an .xlsm Workbook with a modified-to-fit version of this macro
Finally, format the cells to the date format you want.
Please Login or Register to view this content.
I tested the formula, but, because I use US date formats, I can't test the Sub without a lot more work.
Last edited by SamT; 03-27-2014 at 08:15 PM. Reason: typ0s
Sam, not sure that will work. Those "dates" are actually text, and many do not have a day included.
Try this, copied down...
=IF(C4="","",DATE(RIGHT(C4,4),IF(LEN(C4)=7,LEFT(C4,2),MID(C4,4,2)),IF(LEN(C4)=7,1,LEFT(C4,2))))
This will do column C for you, copy the formula to 2 columns over for column E
If you want to have the dates back into C and E, copy/paste values from the formulas to C and E
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
Thank you Mr Dibbins.
Your clever advice worked for the first date column but not the second.
Instead, I converted the second column manually to avoid wasting your precious time.
Regards,
Dave
Well, "Substitute" being a text function, I assumed that even the non US Date formatted strings would convert to UK formatted date strings on a system using that format.
All the strings containing only the UK months and years converted nicely to US months and years. Obviously, a string like "30.3.1948" would not convert for me, but "3.48" converted to Mar, 1948. (When Formatted "mmm,yyyy". "mmm,dd,yyyy" gave Mar 01,1948.
I'm either going to have to give up my VBA hobby, or spend a few months updating my personal programming manual from <=2003 to <=2013, and I much prefer coding VBA than writing about it.
Another version
Formula:Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks