Support Article
Not able to parse date from MS Excel correctly
SA-22505
Summary
Developer is uploading an Excel file and parsing data from it. The MSOParseExcelFile activity is parsing the content, however dates present in Excel are converted into double when mapped to clipboard.
Error Messages
Not Applicable.
Steps to Reproduce
1. Upload Excel file with at least one date field.
2. Create a template to map the Excel value to clipboard.
3. Parse Excel by using the activity MSOParseExcelFile.
4. Observe that Date gets mapped as doubles in the clipboard.
Root Cause
An issue in the custom application code or rules: Dates are stored as integers in Excel (and Datetimes as decimals)
Resolution
Here’s the explanation for the reported behavior:
This is expected behavior.
1. Use Apache POI (Excel interface) or java functions to convert content:
Apache POI has some utilities for this http://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/DateUtil.html, notably http://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/DateUtil.html#getJavaDate(double)
Note: Excel stores dates as the number of days (plus fractional days) since 1900 (and in some cases it can be from 1904). See Microsoft KB article 180162.
Here is a minimal working example how to convert an Excel date to a Java date in java:
Date javaDate= DateUtil.getJavaDate((double) 41275.00);
System.out.println(new SimpleDateFormat("MM/dd/yyyy").format(javaDate));
Which returns:
01/01/2013
2. Import the following packages:
> java.text.SimpleDateFormat
> java.util.DatePublished April 30, 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.