Converting Text to Dates

[Darko_Giac]

Hi all,

I'm having a bit of a brain glitch.

I consider myself a fairly intermediate to advanced Excel user, and I cannot, for the life of me, trouble shoot converting these strings into dates.

This is an Excel export from an online data set, and I would just like to convert the values in column A to proper dates. I've tried everything from just simply changing the option in the drop down menu to Long/Short date, to using the DATEVALUE function, to using the text to columns transposition.

I'm not sure if I'm missing a step, but it just doesn't want to seem to convert these values to dates. The reason I'm trying to do this so adamantly is because I need to then sort the list from earliest, to latest date.

Any help is greatly appreciated!

[Fluff13]

Text to columns works for me, for the Date did you choose MDY?

[Darko_Giac]

Oh my god, I was definitely clicking on the wrong option there.

Thanks so much!

[Pete_UK]

You can use this formula (e.g. in B2):

=IF(ISNUMBER(A2),DATE(YEAR(A2),DAY(A2),MONTH(A2)),DATE(RIGHT(A2,4),LEFT(A2,FIND("/",A2)-1),SUBSTITUTE(MID(A2,FIND("/",A2)+1,2),"/","")))

Format that cell as a date in the style you prefer, then copy down to the bottom of your data.

You could then fix the values in column B and delete column A if you no longer need those values.

Hope this helps.

Pete

[Fluff13]

Glad to help & thanks for the feedback.

[alansidman]

An alternative means is to use Power Query

Please Login or Register  to view this content.

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.