How to parse an Excel date into a date

fabienb1 — Fri, 18 Oct 2024 10:17:07 GMT

Hi everyone,

I try to read an Excel file that has its dates stored as a double ; for example 41275.00 is 01/01/2013 ; or 45537.4080179398 is 02/09/2024 09:47:33

How can I read that ? I can read it as a String, but I can't convert it to a date with TalendDate.parseDate(), and Talend does not support java.util.Date.

I have found many issues realted to date formatting on this community, But none directly related to the specifically Excel format. Do not hesitate to tell me if I missed it.

Regards,

Re: How to parse an Excel date into a date

fabienb1 — Fri, 18 Oct 2024 12:57:07 GMT

I answer to myself : in a tJavaRow

Double excelDate = input_row.<my input value> ;
int days = (int) Math.floor(excelDate);
double fractionOfDay = excelDate - days;
String baseDateStr = "1899-12-30";
Date baseDate = TalendDate.parseDate("yyyy-MM-dd", baseDateStr);
Date finalDate = TalendDate.addDate(baseDate, days, "dd");
int hours = (int) (fractionOfDay * 24);
int minutes = (int) ((fractionOfDay * 24 - hours) * 60);
int seconds = (int) (((fractionOfDay * 24 - hours) * 60 - minutes) * 60);finalDate = TalendDate.addDate(finalDate, hours, "HH");
finalDate = TalendDate.addDate(finalDate, minutes, "mm");
finalDate = TalendDate.addDate(finalDate, seconds, "ss");

output_row.<my column name>=finalDate;

It's better than nothing, but it works.

topic Re: How to parse an Excel date into a date in Data Quality

How to parse an Excel date into a date

Re: How to parse an Excel date into a date