Hi All,
I have attached an excel file with some sample data. Column A has a stringed datatype like Wednesday, January 02 2013 and I just can't seem to find anyway to convert it to yyyy-mm-dd.
I've been at it for a couple days now and have decided to ask for you expertise in this matter.
Any help will be immensely appreciated.
Thanks in advance.
=DATE(RIGHT(A2;4);FIND(MID(A2;FIND(",";A2)+3;2);" anebarprayunulugepktovec")/2;LEFT(RIGHT(A2;7);2))
and format or TEXT with the same format
Last edited by BMV; 01-23-2020 at 04:47 AM. Reason: Typo correction
Thanks BMV for the solution, much appreciated. By the way what does " anebsrprayunulugepktovec" do? I have never seen that before.
FIND(",";A2)+3;2);" anebarprayunulugepktovec")/2 - is shortest way to get number of month from name and it's not depending from regional settings. For some language combination of 2nd and 2rd characters in the month name is unicalOriginally Posted by spittingfire
"_JanFebMarAprMayJunJulAugSepOktNovDec". Find return 2,4,6.... and after /2 we will have number of month.
Howeever I have typo and corrected formula in #2
Last edited by BMV; 01-24-2020 at 12:31 AM.
Thanks for the explanation BMV. I understand it now. Much appreciated.
I have one question about your explanation. Wouldn't "an" the second and third characters in January, return 1, not 2. Then if you divide by 2, you would have 1/2, which would not give the correct number of the month. The only part I don't get now is the divide by 2.
Before strange string the space have been inserted. so "an" started from 2nd character of " aneb…."
it could looks like (FIND(MID(A2,FIND(",",A2)+3,2);"anebarprayunulugepktovec")+1)/2 but longer.
also
(FIND(MID(A2,FIND(",",A2)+2,3);"JanFebMarAprMayJunJulAugSepOktNovDec")+2)/3 or
FIND(MID(A2,FIND(",",A2)+2,3);"--JanFebMarAprMayJunJulAugSepOktNovDec")/3
Another way
=TRIM(SUBSTITUTE(RIGHT(SUBSTITUTE(A2,",",REPT(" ",255)),255),RIGHT(A2,5),", "&RIGHT(A2,4)))*1
Life's a spreadsheet, Excel!
Say thanks, Click *
it's true but only in the case if regional setting allow it. For me it's not so. However the same could be done easy
Formula:
Please Login or Register to view this content.
Yes, I understood the string had a space inserted before an. I get that 2 and 3 characters give one "an," but the month of January is 1. SO what I don't get is the dividing by 2.
=DATE(RIGHT(A2;4);FIND(MID(A2;FIND(",";A2)+3;2);" anebarprayunulugepktovec") = THIS part of the formula returns January as a number, which is 1
=DATE(RIGHT(A2;4);FIND(MID(A2;FIND(",";A2)+3;2);" anebarprayunulugepktovec")/2= I don't understand the divide by 2. 1 divided by 2, does not give 1, which is what they need to be January. Could you just explain the divide by 2 part?
you are wrong. The result of FIND will be 2 . FIND("an"," anebarprayunulugepktovec") return 2 because 1st char is " " , 2nd "a", 3rd "n".
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks