DATE() function throws exception if given number of days

[markbirbeck]

Excel spreadsheets store dates as the number of days since January 1st, 1900. If a CSV file hasn't been correctly exported then this can manifest itself as a value like '40337'. It would be good to convert this automatically to the correct date.

[markbirbeck]

More information on this format is available here:

http://www.cpearson.com/excel/datetime.htm

[Christian-B]

In E_Date class use the following:
public static Date parse(String date) throws ParseException {
try {
return DATE_TIME.parse(date).toDate();
} catch (Exception e){
//Ok now lets try it as a day in numbers.
int days = Integer.parseInt(date);
if (days > 60){
days--; //Fix the Excell bug of counting none existing 29 Febuary 1900 as day 60
}
GregorianCalendar calendar = new GregorianCalendar (1900, 0, days, 0 , 0, 0);
return calendar.getTime();
}
}