Support Article
Procedure to convert number of days to date
SA-19438
Summary
User is trying to parse an excel file with activity MSOParseExcelFile to parse the excel file, there are some date columns in the spread sheet and if the value is 1/11/2016. The activity is returning the value as 42380, which is the number of days from 1900.
User wants to convert this number(42380) to actual date. And has used Out-of-the-box (OOTB) function @addToDate(), but is getting a value which is two days greater than expected.
Error Messages
Not Applicable
Steps to Reproduce
- Use the OOTB activity MSOParseExcelFile to parse an excel file with dates in it.
- See the result of the date column, which is a numeric value but not date.
Root Cause
A third-party product issue with Excel saving date columns as number of days starting from January 1st 1900 as 1. So, when parsing with MSOParseExcelFile activity we are getting the number of days from excel as the value in that column.
As per Microsoft kb article 214326, excel considers year 1900 as a leap year which is not, so an extra day is added into the number of days returned from the excel file.
Resolution
Perform the following local-change:
After getting the data from the MSOParseExcelFile, one must use function addtodate() with parameters 31st december 1899 as the start date and the number of days returned from the parse activity and other parameters as zero.
For the number of days specified in the summary, use @addtodate("18991231T000000.000 GMT", 42380,0,0,0) which returns 1/11/2016.
Published February 12, 2016 - Updated October 8, 2020
Have a question? Get answers now.
Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.