Excel Date Formats

Situation: I am setting up a new client who sends in an excel file with multiple tabs
of data. Therefore we have to define and set up the schema as Advanced
Database. Some of the dates provided in the file are stored not as a
string, but as the actual Excel Date value (i.e. 07/01/2011 is stored as
40725). I do not want to have to go in and change the client's file before
running through Adeptia, however I cannot figure out a way in the Adeptia
mapping to take the 40725 value and translate to a date value that can be
processed in the map. Is there a function / way available to do this?

Solution:

The Data Mapper has an in-built function that can handle this situation. In Adeptia v5.3 there is a Mapper Utility Class called dateAdd, where you can specify the starting date and add a certain value to generate an output date.

This is useful in your case since the serial date value stored by Excel is the number of days since 1/1/1900. Therefore, by adding the value stored by excel to this date, you'll get the needed output date. This can be accomplished in the Textual Rules section of the mapper:

For previous versions of Adeptia, this method is available here:http://support.adeptia.com/entries/21261922-using-dateutils-method.

Example:

o Input Date: 1/1/1900
o Input Date Format: MM/dd/yyyy
o Output Date Format: MM/dd/yyyy
o Year need be added/subtracted: 0
o Month need to be added/subtracted: 0
o Day need to be added/subtracted:$Input_Schema_testschema_/DB/testdb/issue_date (serial number value from your source)

This will add the serial date value from your source to 1/1/1900 (base date of Excel) to generate the needed date.