Mulesoft add excel date serial number to date

Question

I'm reading an excel file data, one of the column is 'DateTime'(i.e. 01/01/1990 00:00:00) but Anypoint read it as string type 'excel date serial number'(i.e. 33257.415972222225). How do I transform in DataWeave the date correctly?

ex: date : "01/01/1990 00:00:00" as :date {format: "MM/dd/yyyy HH:mm:ss"} + "P$(33257.415972222225/12)Y"

Can you please provide this an answer to your own question and mark it as correct? — jerney, Oct 25 '18 at 00:29

score 0 · Answer 1 · answered Nov 04 '18 at 12:48

There is an existing answer for converting Excel dates to Unix dates (epoch time) that can be easily adapted to DataWeave. Note that Excel in Windows and Mac use a different starting date so the formula varies accordingly.

script

%dw 1.0
%output application/json
---
{
  dateExcelWindows: (((payload as :number) - 25569 ) * 86400 ) as :datetime,
  dateExcelMac: (((payload as :number) - 24107 ) * 86400 ) as :datetime
}

input (this is June 9th, 2011 10:30 AM in Windows Excel):

"40703.4375"`

output:

{
   "dateExcelWindows": "2011-06-09T10:30:00Z",
   "dateExcelMac": "2015-06-10T10:30:00Z"
}

Mulesoft add excel date serial number to date

1 Answers1