Consolidating Multiple Date formats into one

[ardykav]

Hi,

I have a database which cannot be changed. When I extract all the data out there are several fields in date format. However over the time it has been populated into about 5 different date formats from DD/MM/YY to MM/DD/YY to the aforementioned with the time after it etc etc.

I was wondering is there some code which will basically take the whole column and no matter what the format of the date is convert it to one simple format?

All help greatly appreciated

 

[etaf]

how would you know which dates the following actually is

1st Aug 2016 (01/08/2016) , 12th Jan 2016 (12/01/2016)
could be
8th Jan 2016 (08/01/2016) , 1st Dec 2016 (01/12/2016)

MM/DD/YYYY
01/08/2016 , 12/01/2016
changes to
DD/MM/YYYY
08/01/2016 , 01/12/2016

both formats are valid and also valid dates , so how would you know which format is the the correct date

any other way to be able to tell the difference on the spreadsheet at all ?

 

[gallen]

I'm probably missing something but what happens if you select all the relevant cells and then change the format of the cells?

 

[ardykav]

Good point. There are about 25 columns with date so think some co relation can be made just need a starting point really. Regards gallens question it doesn't format all the cells correctly. Even when clicking short date sometimes will still leave the time I'm the cell

 

[etaf]

ok, so you have 25 columns in 1 row that has various date formats - is there any column you can rely on as accurate date ?

 

[zoliidais]

Hi,

Not sure if this is the case but if this is just one time thing (not macro or smth) then:

Try to copy this column to Notepad, and then paste it back into excel.
It should change format to your local settings for all dates in column.

 

[ardykav]

There are 25 columns and about 1500 rows. However yes there is one column that would always be in the format I want the others to be in

 

[etaf]

would that be able to define the date somehow
so if the other column had
04/07/16 we would know from the column that it was a month of April and so could make that the correct date and format ?
or could the month still be July ?

 

[ardykav]

Yes so Column G will always have the right format and any other columns would be after this date. Would it be possible to write something in that would make sure that it follows a date format that is after this date?

would that be able to define the date somehow
so if the other column had
04/07/16 we would know from the column that it was a month of April and so could make that the correct date and format ?
or could the month still be July ?

 

[etaf]

you probably need VBA - but can you put a sample onto a share like dropbox or onedrive