LinkBack URL
About LinkBacks
HI,
I have a text "Sunday 2 November 2014" in cell A19; and would like to change it to "2-Nov-2014" as a date, in cell B19.
Is there any formulas available to correct the problem?
Cheers
HI,
I have a text "Sunday 2 November 2014" in cell A19; and would like to change it to "2-Nov-2014" as a date, in cell B19.
Is there any formulas available to correct the problem?
Cheers
=date(right(a19,4),month(datevalue(trim(mid(substitute(a19," ",rept(" ",60)),120,60))&"1")),trim(mid(substitute(a19," ",rept(" ",40)),40,40)))
Or, slightly shorter...
=--MID(A1,FIND(" ",A1,1)+1,99)
format to the date you want
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
Hi,
FDibbins,
That formula couldn't be formated despite several efforts.
But, it is sorted now.
Thanks for your co-operation.
Perhaps your data doesnt quite match the sample you gave then?
Sunday 2 November 2014
B1=--MID(A1,FIND(" ",A1,1)+1,99)
C1= the same thing, formatted as dd-mmm-yy (or dd-mmm-yyyy if you want)
If thats not working, then just out of curiosity, can you provide a sample workbook?
Well, it did worked for another version of MS office.
Cheers
There is nothing in that formula that older versions of excel could not use, but as long as you got what you needed
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks