Date issue

[tanro]

I have a date column with thousands of lines (obtained from some external source) in the following format:- mm/dd/yyyy hh:mm (e.g. 07/20/2009 18:30). When I opened the file, anything with day >12 gets classified as text rather than date, anything with day <13 gets misinterpreted with the month and day switched round. Is there a way to get this column formatted as it is without having to change any system setting?

 

[wsjackman]

how is your computer defaut date set up?
you can change it and maybe get the dates to transfer correctly

if this text to column you can define the way the date is to be transfered
or you can have it all come in as text and transform it into a date with a formula

 

[tanro]

Thanks wsjackman for the quick response. The company's PC default date setting is UK format and cannot be changed. I prefer to format all to text first and transform it into a date via a formula. Can provide some guidance on how to do this? When I tried to format the cell to text, "07/07/2009 18:30:00" for example becomes " 40001.7708333333".

 

[wsjackman]

I really meant to import it as text rather than transform after

try

=if(A1=0,date(value(mid(a1,7,4)),Value(left(a1,2)),Value(mid(a1,3,2))),DATE(YEAR(A1),DAY(A1),MONTH(A1)))

(third attempt to submit)

 

[barry houdini]

I think you'd need to test whether A1 is text or number, e.g. like this

=IF(ISTEXT(A1),REPLACE(MID(A1,4,99),4,0,LEFT(A1,3))+0,DATE(YEAR(A1),DAY(A1),MONTH(A1)))

custom format result cell in required format, e.g. dd/mm/yyyy hh:mm

 

[wsjackman]

Barry is, as normal, correct

I got sloppy for my test for text

=if(value(A1)=0,date(value(mid(a1,7,4)),Value(left(a1,2)),Value(mid(a1,3,2))),DATE(YEAR(A1),DAY(A1),MONTH(A1)))