2179631 - When aquiring dates from Excel Lumira Desktop displays dates four years earlier than what is displayed in Excel.

When Lumira desktop aquires dates from an Excel file Lumira displays the date four years earlier than the dates show in Excel.

Lumira Desktop 1.27

1. Create a Lumira document by acquiring data from a Microsoft Excel table that contains dates.

Microsoft Excel supports two different date systems, the 1900 date system and the 1904 date system. The 1904 date system was implemented for the Macintosh version of Excel due to the early Macintoshes not supporting dates bfore January 1, 1904.

Lumira Desktop supports the 1900 date system which means if a Excel file is set to use the 1904 date system the dates are four years off.

There are two solutions to this scenerio, the first is to update the Excel document to use the 1900 date format and the second is to create a new calculated dimension in Lumira that adjusts the date.

 

Updating the Excel document to use the 1900 date format.

1. Open the Excel file in Microsoft Excel.
2. Under Options select Advanced.
3. Under the secion "When calculating this workbook:" uncheck "Use 1904 date system".
4. For more information please see https://support.microsoft.com/en-us/kb/180162

Creating a new Calculated measure in Lumira Desktop to adjust the date.

1.  In Lumira Desktop with the Lumira file open navigate to the Prepare room.
2. Create a new Calculated Dimension.
3. Create the following formula:

AddYearToDate({Date}, 4) + 1

This formula will add 4 years to the date and add an extra day for the leap year.

Please see Microsoft kbase article 180162 for more detailed information: https://support.microsoft.com/en-us/kb/180162

• MS Excel
• XLS
• XLSX
• Pivot Table
• spreadsheet
• 4

, KBA , BI-LUM-DIS , Lumira Desktop/Discovery , Product Enhancement

SAP Lumira, desktop edition 1.0