Convert Text into Date

Hello,
could you please help me with formatting text into date, when text is in format dd/mm/yyyy and the date format should be like this mm/dd/yyyy?
I tried Text import wizard and in helped, but some cells remained unchanged. I also tried some macros posted in here, but none of them really helped in my case

Thank you so much

Try this formula:

=DATE(RIGHT(A1,4),MID(A1,4,2),LEFT(A1,2))

where A1 contains your text-date. Format the cell as a date. You may need to use semicolons ( ; ) instead of the commas ( , ).

Hope this helps.

Pete

Hello Pete, excel is returning "#VALUE!" for this formula..

Post an example file showing a range of your "date" values.

Pete

Here is the column with dates, that cannot be formated

Your dates start in A2, not A1. Also, you have some "dates" formatted as d/m/yyyy and also some hyphens instead of dates.

So, put this amended formula in B2:

Formula:

Please Login or Register  to view this content.

Format as you wish to see the date, then copy this down.

Hope this helps.

Pete

Yes, you could use SUBSTITUTE to change those full stops to a slash, like this in B2:

=IF(A2="-","",DATE(RIGHT(A2,4),SUBSTITUTE(MID(SUBSTITUTE(A2,".","/"),FIND("/",SUBSTITUTE(A2,".","/"))+1,2),"/",""),LEFT(SUBSTITUTE(A2,".","/"),FIND("/",SUBSTITUTE(A2,".","/"))-1)))

The first date like that occurs in row 590.

There is also another problem date in row 575, where there is a double-slash. The same approach could be taken to change that // to /, or you could begin the formula with something like:

=IF(LEN(A2)>10,"error", ...

to alert the user to change the source data, as there could be many more such errors (like one date with letters in).

Hope this helps.

Pete