• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

converting text to date format

J

jules meredith

New Member
I have imported data that displays a date as a text string in a single cell in the style of "Sunday 10th November" and "Monday 11th November" etc. I would like to convert this text to a date format as dd/mm/yyyy. I have tried using =datevalue() but it always returns #Value. What am I doing wrong?
 
Would you please upload a sample file and/or include the years in your two examples. Are the days always shown using two digits (e.g. would it be "Saturday 9th November" or "Saturday 09th November"?
 
I will upload a sample when I get back home.

The years are not included in the text version but I was hoping that excel would default to the current year i.e. 2019.

The day figures are not always double digits, so could be 1st 2nd 3rd .......31st.
 
Hi ,

Try this :

=DATE(2019, MONTH(MID(A1, MAX(IFERROR(FIND({"st ","nd ","rd ","th "},A1),0)) + 3, 99) & 0), MID(A1, MAX(IFERROR(FIND({"st ","nd ","rd ","th "},A1),0)) - 2, 2) + 0)

This is an array formula , to be entered using CTRL SHIFT ENTER.

I have assumed your string is in cell A1.

Narayan
 
Hi Narayan, That works brilliantly, thank you for your help.

Hi Bosco, Your solution also does exactly what I need, Thank you also.

As a by question - As both these solutions work as requested - Is there any difference in using an "array" formula in curly brackets or a non array formula? Or does it not really matter?

And again thanks to both of you.
 
jules meredith said:
As a by question - As both these solutions work as requested - Is there any difference in using an "array" formula in curly brackets or a non array formula? Or does it not really matter?
Click to expand...
Hi ,

More than the matter of an array formula , I suggest you go with bosco_yip's solution , since it uses a far lesser number of excel functions , and may be simpler to understand.

Each solution uses a different strategy , and it will be worthwhile to get to know how each works so that you can use it or at least parts of it where ever possible.

The usage of MONTH(text & 0) to get at the month number is one such part.

Bosco's solution replaces the text portions "st" , "nd" , "rd" , "th" by the slash symbol "/" , and then converts this to a number , which Excel recognizes as a date in the current year.

Narayan
 
Another approach might be to examine your import process to see whether you can use Power Query for that purpose.
Within PQ, I split columns first on 'space' then on transition from digit to letter. Combining the digits and the month with '/' as a separator allows PQ to parse the remaining text as a date, which it then loads into Excel as a date serial number.
 
Depending on your data, this could just be as simple as a format change. If you select the cell with "Friday 1st November", right-click it and select "Format Cells", then if you select "Text" and the Sample shows 43770, all you need to do is re-format your dates.

Chandoo-JulesMeredith.jpg
 
You must log in or register to reply here.
Back
Top