Interpretation of Dates going wrong

[#1]

Hi All,

I would like to use an Excelsheet for analysis, but I have some issues with the interpretation of a date field. In Excel the date records look something like this:

9/30/2014 5:06:55 PM
9/30/2014 5:12:19 PM
9/30/2014 5:12:39 PM
9/30/2014 5:15:59 PM
9/30/2014 5:58:36 PM
10-1-2014
10-1-2014
10-2-2014
10-2-2014
10-3-2014

All date-records should have the MM/DD/YYYY format, but Excel shows the first records as text. The last records are interpreted as date, but with a wrong format(DD-MM-YYYY instead of MM-DD-YYYY).

Is there an easy way to read all these records in the right format without having to change the Excel-data?

[#2]

Thanks for all. For now I'll stick to my own solution 😉 : not the most elegant solution, but (with my limited knowledge about scripting) for me easy to understand.

regards Perry

[Clever_Anjos]

Maybe

1. DATE(FLOOR(ALT( 
2.   Date#(F1,'M/D/YYYY h:mm:ss TT'), // Try first format 
3.   Date#(F1,'DD-MM-YYYY') // then second  changed here!
4. )))  as Date 
5. INLINE [ 
6.     F1 
7.     9/30/2014 5:06:55 PM 
8.     9/30/2014 5:12:19 PM 
9.     9/30/2014 5:12:39 PM 
10.     9/30/2014 5:15:59 PM 
11.     9/30/2014 5:58:36 PM 
12.     10-1-2014 
13.     10-1-2014 
14.     10-2-2014 
15.     10-2-2014 
16.     10-3-2014 
17. ];

[campbellr]

Hi Marco,

The problem with your solution is the dates are all september and october in m/d/y format but you are switching them to d/m/y for days between 1 and 12. This is the problem that Perry was having to start with.

Ron