Logic behind Number to Date conversion at Excel....

Number of time we have encountered, that some times our number are getting showed in date format and we simply change the format and read it as number as required.


Today while going through the code, I realized the need to understand the logic behind it.


Our source files comes in Excel format, wherein Date column (say as Purchase Date) comes in various formats e.g. 1-Jan-2011, 01-01-2011, 01-January-2011, 01/01/2011, 1-Jan-11 and 40544. All formats are readable except last one 40544.


Now question for me was, how can I convert this 40544 to date? How will my code understand that this is a number and what way should I convert this? Fortunately logic to decode this was available in other mapping.


How do the do it?
They first take out the length of a string (its a date but comes as a string port), first validates all the mentioned formats mentioned above (except number format) and at last if the nothing is true and if length is less than 6 - not sure why they check for less then 6 date; might be they want to stick only to 21st century - they apply below logic.


If we write 1 as a number of excel cell and convert that to date format it appears as 1/1/1900


i.e. for number 1 it considers as 1-Jan-1900. So if we specify 2 it will be 2-Jan-1900. Logic is we need to add that many number of days to 1-Jan-1900. If number is 40544 means 40543 days after 1-Jan-1900. This addition of 1 is not always true as there might be some leap years comes in and changing the date. If we negate 2, logic works fine


Below query will give the results in oracle --


select to_char(to_date('1-Jan-1900','dd-mon-yyyy') + 40544 - 2,'dd-mon-yyyy')  from dual


Query above will give result as 02-jan-2111, which is mathcing with that of excel sheet.

In informatica the logic is as below

ADD_TO_DATE(TO_DATE('01/01/1900', 'dd/mm/yyyy'),'D',TO_INTEGER(NAME))

Add_to_Date will take 3 inputs,
1. Date in which you want to add
2. What you want to add - D/DD/Day means day. M/MM means month and Y/YY means year. Please see informatica help for further information
3. Last is how many numbers you want to add.