Excel and Date

Hi

have an excel file coming in with a row with a date formatted as DD-MMM. AETL is reading this as a String and not returning the underlying value.

For example it displays as 31-DEC in the workbook and 31-DEC in the AETL transformation designer. We need to format this as a real date and so Reformat Date Transforantion action was used with the expectation that it would take the underlying value and return a date as YYYY-MM-DD

However, because it read it as a string it appended the current year onto the string making 31-DEC-2024 where the correct value should have been 31-DEC-2023

Also, have tried "Ignore Format Option" on the reader and this displays the underlying numeric value for the date. Would need to then get this into YYYY-MM-DD format for SQL.

Any way around this?

Thanks

Excel stores dates as sequential serial numbers so that they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,447 days after January 1, 1900.

The easiest way to address this problem is to use calculation transformation function

The following script will convert the number to date

Thanks for this and I can verify it has resolved our issue. My only comment would be that whilst I had considered a script solution and understand serial dates, I would not have immediately considered the StrToInt() function as it isn't (to me) intuitively related to date manipulation. I can see that a new article on this topic has been added. Thanks again.