Hi Everyone,
I have several hundreds of text dates that are formatted like this; "22OCT2013". I have tried date(Left);MID();right()) but it does not work. I have even tried datevalue which I am sure is thr wrong way of doing this and I got #VALUE. Even used the search and replace function to replace OCT with 11 but it does not work to format it to date.
How do I convert 22OCT2013 to an actual date of DD-MM-YYYY?
Try:
Formula:
Please Login or Register to view this content.
let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source
If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE
Walking the tightrope between genius and eejit...
it returned #VALUE!
Is the numberformat of the cells text, or is it a custom date format?
Maybe attach a workbook, so I can see the cell numberformat.
Not sure how to upload on here the workbook but the format is "General".
From the rules:
I'm keen to see your workbook, as the formula I posted works for me, with 22OCT2013 entered as text or as a date...To attach a file, push the button with the paperclip (or scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button.
Hi,
thanks for sharing how to upload file on here.
Attached is the file.
Yes, formula works for me in your workbook, with no #VALUE error; in cell B1, enter:
Formula:Please Login or Register to view this content.
Copy down, and format column B as Date.
Sorry, I can't add attachments from this computer (company system policies prevent it) to demonstrate, but that formula works for me.
works for me, in your workbook...Formula:Please Login or Register to view this content.
One more approach..
=A1+0
Regards!
=DEC2HEX(3563)
If you like someone's answer, click the star to give them a reputation point for that answer...
That is real odd. It did not work.
I have the Swedish version so we use ";" instead od ",". I tried putting " between the date format;dd-mm-yyyy but it didnt work. When I dont put the ", then I get #NAME.
Thank you for your help. At least you tried helping me. Really appreciated:=)
Originally Posted by grattis
May have been helpful to mention Swedish from the start...
OCT is English. Think you'll find Swedish is OKT. If your locale settings are set to Swedish, it won't recognise OCT as a month.
Try:Formula:Please Login or Register to view this content.
Oh, you may also find you need to enter formulae in Swedish.
DATEVALUE would then become DATUMVÄRDE...
SUBSTITUTE becomes BYT.UT...
hej,
it was originially in Swedish but I had it changed to English. The only difference is that I still have to type semicolon instead of comma :=)
I got it to work. I replaced OCT with -10- and then used date function with right,mid and left.
I had a question.
How do I handle date like this
I was able to convert the dates but when I had a single digit day, it did not work. Do you any ideas as to how convert it?
Please Login or Register to view this content.
Thank you
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks