topic Re: Convert Excel date number value to proper date in Talend Studio

Convert Excel date number value to proper date

_AnonymousUser — Sat, 16 Nov 2024 11:16:56 GMT

I have a csv file which was exported from an excel file. 1 of the columns is a date value but displayed in the underlying number excel uses to store dates i.e its not formatted as a date its a number that represents the time elapsed since Jan 1 1900 ( I'm open to correction on it)
I was wondering whats the best way to convert it into a date I can store in a date field of a schema definition?
I'm not sure how to phrase the query so apologies if its a common query but searching the forum I came across this post from 2008 and I was wondering if there was a more mainstream method the community uses since then?
Old forum post

Re: Convert Excel date number value to proper date

_AnonymousUser — Sun, 22 Mar 2015 15:42:35 GMT

It seems I can't post links using the WSYIWYG editor
Also this was incorrectly tagged as a db2 database issue. its related to excel date's underlying number value and how to convert that to a date value acceptable to other systems and format it into a user friendly representation

Re: Convert Excel date number value to proper date

Anonymous — Mon, 23 Mar 2015 14:51:22 GMT

Convert Excel's number into the number of milliseconds past Jan 1, 1970 00:00:00 GMT and then use the java Date constructor.

Re: Convert Excel date number value to proper date

Anonymous — Mon, 23 Mar 2015 14:54:55 GMT

Convert Excel's number into the number of milliseconds past Jan 1, 1970 00:00:00 GMT and then use the java Date constructor.