Source is ddmmyyyy, but converts to mmddyyyy once pasted to excel

Hi,

I have data coming in from a source (e.g. Oracle database, 3rd party app) that I have no control over the date formats - currently dd/mm/yyyy hh:mm:ss. When I paste the date values on Excel, they get converted to mm/dd/yyyy hh:mm:ss. I want excel to not convert it to that format but still recognize them as dates so I can use MONTH(), DAY(), and other date arithmetic functions. Would anyone know how to do this without exploring to VBA?

Hi,

I used text to columns to separate the date and the time, along with AM/PM options. But that's what's weird. Some columns are recognized as full datetime so they have AM/PM, but some don't have (those with the first value >=12, since it defaults to mm/dd/yyyy). I have uploaded a new sheet showing the MONTH() result of a column, and it considers the leftmost value as month. You can see there that the first value is 1 instead of 11 for 01/11/2012 date. Some "unrecognizable" data have #VALUE! in them.

Hi

Have a look at the attached, might be of help.

Hi Fotis,

Thanks! This solution works. How were you able to do it?

On second check, the 3rd column you did Text to Columns to does not reflect the right dates. The data is ordered based on "Open Time", and the first entry is November 1, 2011, not January 11, 2011.

Hi Kevin UK,

I've used your formula to get an idea of how to extract each datetime parameter and sum them up once they have been converted to DATE() and TIME() values. Thanks for your help!

Here's the result.

Hi Winterburn

Glad you got it sorted, I was in a hurry and misread your post.
Those formula's in Columns B,C,D.F,G & H if you wrap them in a VALUE function, +0 or use the double unary operator at the start. They will return numbers instead of text if you require that.
EG:
=--IF(your formula etc)

=VALUE(IF(your formula etc)

=IF(your formula etc)+0

Kevin