Different date formats

[alexmols]

Hello all,

i have received an automatically generated file which contains a long list of dates which have two different date formats, unfortunately. Examples of these two formats are:

1-12-2010
1/13/2010 12:23:29 AM

Note:
the first format is d/mm/yyyy
the second format is m/dd/yyyy (hh:mm:ss AM)

Is there a way by which I can get the column next to it, to contain one single date format, preferably dd/mm/yyyy

Thanks in advance,
Alex Mols

 

[jmthompson]

Are both sets of dates recognized as dates by Excel, or are they text?

 

[macdca]

I assume you have copied the date you wish to format into the new column and then: format, date format and choose what you want?

 

[alexmols]

Thank you for your quick reactions!

The first example (1-12-2010) is indeed recognised as a date, but the second one (with AM) isn't. It gives as format "standard".

I have indeed copied the date into a new cell and chose my preferred date format as format, but this only works for the (1-12-2010) and not for the other one. Even when creating a custom format and labelling:
1/13/2010 12:23:29 AM
as
m/dd/yyyy hh:mm:ss AM/PM
does not enable me to create a simpler format (or at least I do not know how)

 

[jmthompson]

Try this formula, update A1 to the appropriate cell

=TEXT(A1,"dd/mm/yy")

 

[alexmols]

Try this formula, update A1 to the appropriate cell

=TEXT(A1,"dd/mm/yy")

This doesn't work for the date which is given as:
1/13/2010 12:23:29 AM

Any other idea's? It's over 3000 dates so I really don't want to do it by hand...

 

[macdca]

Try Tools, Options, calculation and 1904 date formats, does the formula work then?

 

[jmthompson]

Here's a convoluted formula you can try...

=CONCATENATE(MID(A2,(FIND("/",A2,1)+1),2),"/",LEFT(A2,(FIND("/",A2,1)-1)),"/",MID(A2,(FIND(" ",A2,1)-4),4))

 

[alexmols]

I've got excel 2007 (and it's in Dutch instead of English) which makes I can't find the menu you suggested. Do you know where to find this menu in excel 2007?

I did find out that the problem lies with the switch in dd/mm or mm/dd. Since "we" in The Netherlands use a standard format of dd/mm/yyyy but some of the dates are shown as mm/dd/yyyy.

Because even if I remove the time and AM/PM from a cell, it still doesn't convert it correctly.

 

[alexmols]

Here's a convoluted formula you can try...

=CONCATENATE(MID(A2,(FIND("/",A2,1)+1),2),"/",LEFT(A2,(FIND("/",A2,1)-1)),"/",MID(A2,(FIND(" ",A2,1)-4),4))

this is meant for the other topic I think?