Calling the Excel gurus.
You have been given a table of data in Excel, extracted from a text source, thousands of rows. One column is a date. It’s displayed as say dd/mm/yyy.
It could be '16/10/2016 or '6/06/2016 or '6/6/2016
The value is right aligned in the column and on checking you can confirm it’s text. bugger
You need to get the values into numerical dates, for reporting purposes whatever display format you need.
Now if the column of text dates all have the leading zeros ie the values are all 10 characters then the task is easier but in any case I break the d, m, and y text components up, convert them to numbers and them combine them back together using date(y,m,d)
So if all the strings are in the format '0d/0m/yyyyy then the formula: =DATE(VALUE(RIGHT(A2,4)),VALUE(MID(A2,4,2)),VALUE(LEFT(A2,2)))
… does the job.
If some of the date strings are missing the leading zeros on the day or month components it gets rather messier to build the formula which works on all the strings:
If the four permutations '0d/0m/yyyy or 'd/0m/yyyy or '0d/m/yyyy or 'd/m/yyyy are possible:
=DATE(VALUE(RIGHT(A5,4)),VALUE(MID(A5,FIND("/",A5)+1,FIND("/",A5,3)-(FIND("/",A5)+1))),VALUE(LEFT(A5,FIND("/",A5)-1)))
works … well sort of.
There is probably alternative, equally clunky constructions.
But there must be a more elegant, less bulky, less prone to error, faster way(s) to do it than the above?
Using delimiters and text to columns maybe?
Thank you for any opinions.
PT