Our royalty software may encounter problems when importing dates.
Microsoft Excel stores dates as sequential numbers known as serial values. Excel stores times as decimal fractions because time is considered a portion of a day. The serial value represents the number of days since 1900-Jan-0, plus a fractional portion of a 24 hour day.
Issue 1. Import preview is displaying dates as numbers
If the import preview screen is showing dates as a number (the serial value) confirm that the Excel workbook source file is closed. If not, close the workbook and restart the import process. This will resolve most problems with dates.
Issue 2. Source file displays dates as number
The source file will display as numbers if 1) dates are formatted as numbers or 2) the display option is set to show formulas in cells.
To check the formatting selecting the a cell, right click and select Format cells. If the format is not date, change the format to date. If this works change the format for all cells in that column to date.
If changing the format does not correct the problem go to Files > Options > Advanced > Display Options and deselect (uncheck) “Show formulas in cells instead of their calculated results.”
Issue 3. Import preview displays incorrect dates
Some Excel source files use non-standard date formats or calculations to display the date. In these cases dates may not be interpreted correctly when imported. If the import preview screen shows incorrect dates convert the data value to a text value by following the steps below.
- Create a new column next to the column containing dates and give it a column name.
- Create a formula in the new column as follows: =TEXT([date cell],”mm/dd/yyyy”). The [date cell] is the cell in your spreadsheet containing the original date. “mm/dd/yyyy” corresponds to your system’s regional settings for displaying dates (e.g. in the United States use “mm/dd/”yyyy”, in the United Kingdom use “dd/mm/yyyy”).
The resulting column will contain text information. Map this column instead of the original date column when importing.