Skip to main content

This content has been archived and is no longer being updated. Links may not function; however, this content may be relevant to outdated versions of the product.

Support Article

Procedure to convert number of days to date



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

  1. Use the OOTB activity MSOParseExcelFile to parse an excel file with dates in it.
  2. 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.


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

Was this useful?

100% found this useful

Have a question? Get answers now.

Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.

Did you find this content helpful?

Want to help us improve this content?

We'd prefer it if you saw us at our best.

Pega Community has detected you are using a browser which may prevent you from experiencing the site as intended. To improve your experience, please update your browser.

Close Deprecation Notice
Contact us