Hi,
I am using a public data source to extract data and the date format is something I can't fix into an excel recognized format. Example below
Sun, Nov 10, 13
Sat, Nov 9, 13
I have tried mid-function to extract the constituent pieces but the character length is not consistent as the date could be 1 OR 2 numbers, so I haven't been able to work out a solution.
The ideal format is:
dd-mmm-yyyy.
Help appreciated, thanks.
This works in the USA
Then format the cell in appropriate Date formatFormula:
Please Login or Register to view this content.
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
Try
=REPLACE(A1,1,5,"")+0
And format the cell with the formula as dd-mmm-yyyy
A1 = "Sun, Nov 10, 13"
A2 = DATEVALUE( RIGHT(A1, LEN(A1) -5) )
A2 held an excel date serial number, so then I just set the cell format to dd-mmm-yyyy and it was fine.
Well, the way this works is it trims the day-of-week off the front and then lets excel figure out the remaining "mmm dd, yy" string. You might need to make that -5 more intellingent if the day code isn't always three letters long, like if Thursday is "Thurs" or something, by using FIND(",", A1, 1)+1 for example.
I have tried all 3 of these formulas and each one returns #VALUE! error. What am i doing wrong?
That would mean your cells don't contain 'Exactly' what you've posted.
Perhaps they are actually real dates already, and all you need to do is go into format Cells, and change the format to
Custom
dd-mmm-yyyy
Can you post a sample workbook?
date formatting - rank.xlsx
File attached. I can't see any difference between the file and what was posted - and i can't format the data as serial numbers in the pre-existing format.
probably missing something basic..>!
There is something wierd about the data in column A
If I select A2, and press F2 then Enter, then it works.
It's something about how the data is imported.
Try copying A2:A21
Then Paste Special Values right back ontop of itself.
Yes I tried that too Jon, F2-enter and it calcs file, copied it off to the side and pasted back, it worked fine, copy, paste without moving the cursor, it worked fine
Not sure what the problem is but I will check further
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
dchubbock, where is this data coming from?
OK this seems to work...
=DATEVALUE(MID(A2,6,99))
When I try copy paste special values i get the same Value error.
I've tried re-importing and pasting in different ways but no joy (the data is originally from an HTML webpage)
Very frustrating. Ideas most welcome
Did you try my suggestion in post # 11?
This seems to contradict what you said in post #9Originally Posted by dchubbock
Yes, same result: #VALUE!
Hi Jon, FDibbins made the second comment - maybe he managed to past special values but it didn't work for me...
My Bad, sorry...
What is your system's default date format? (although I don't think that would matter in a date using spelled months..)
If you format a blank empty cell as General.
And type in a date
What format does it show by default
What exactly did you type?
How exactly does it show it after you press enter?
see the attached where I used my formula from post # 11
No worries Jon
If I type 20/03/2013 in a cell with general format applied, then press enter it changes to a date format, it views exactly as typed.
If I type 20-March-2013 in a cell with general format applied, then press enter it changes to a date format, it views 20-Mar-2013
FDibbins - when i opened the attached file all formulas return value error, inc. your latest one in column G.
Ok that is strange then, this what I get...FDibbins - when i opened the attached file all formulas return value error, inc. your latest one in column G.
Date rank rank2 Formula 1 Formula 2 Formula 3 Wed, Nov 20, 13 Wed, Nov 13, 13 Tue, Nov 26, 13 Tue, Nov 19, 13 Tue, Nov 12, 13 Thu, Nov 7, 13 Thu, Nov 21, 13
Is this the only time you are getting strange/wired results? I wonder if you need to repair/reinstall Office?
This worked for me
=--TRIM(MID(A1,6,10))
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
This formula
=DATEVALUE(MID(A2,6,99))
and this one...
DATEVALUE( RIGHT(A1, LEN(A1) -5) )
and this one..
=REPLACE(A1,1,5,"")+0
all essentially do the same but none will work with UK regional settings because Nov 10, 13 isn't a valid date format in the UK
Audere est facere
Try
=(MID(SUBSTITUTE(A2,",",""),9,2)&" "&MID(A2,6,3)&", "&RIGHT(A2,2))+0
Yep, that one works in UK Jonmo, you could also try this
=SUBSTITUTE(REPLACE(A2,1,9,""),", ",MID(A2,6,3)&20)+0
and format result cell in required date format
Nice,
That one works in Either UK or US date format.
Assuming the year is always in the 21st century
Yeah, I suppose you don't need the 20, it works with
=SUBSTITUTE(REPLACE(A2,1,9,""),", ",MID(A2,6,3))+0
and then the years will be converted as per your regional settings
Good catch, DDL
Fantastic! That formula works perfectly.
Thanks everyone for all your help!
Dan
You're welcome.
Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.
Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks