AnsweredAssumed Answered

Caution: BOM saveas xls, converts numbers to date formatting

Question asked by Cad Admin on Jun 19, 2018
Latest reply on Jul 12, 2018 by Cad Admin

We have a BOMs, we created, we saved it out as a excel(xls) file.  Had issue with 2 lines as it has converted the numbers to dates.


Example: Number is SolidWorks BOM, 5404-8-4   → converted to 8/4/5404 in xls file

Example: Number is SolidWorks BOM, 3051-4-4   → converted to 4/4/3051 in xls file


I contacted my VAR, this was their response:


It is a known issue identified in solution S-029242.
     Text with "#-##" formatting in SOLIDWORKS tables or bills of materials (BOMs) is displayed with a date of when it was exported to Excel®. Is there a way to export the table/BOM so that the text formatting in SOLIDWORKS is maintained?

     This is a problem with the way Excel auto formats text. When opening a new Excel worksheet and typing in the same values used in the SOLIDWORKS table (i.e. 1-24), Excel will format it to, 24-Jan. When trying to format this cell and changing it to text, the number 39471 appears. This value is the number of days between January 1, 1900 and the date (1-24) specified; this is the proper behavior in Excel.

Workarounds to export the text with the formatting specified in SOLIDWORKS are to:
      1. Export to a text file and open the text file in Excel selecting to import as Text.
      2. Enter an apostrophe (') before each cell that contains what Excel sees and formats as a date.
      3. Use an Excel base BOM/table where the formatting is already specified inside of the Excel file.

     This issue has been identified and assigned as an enhancement request to SPR 457621 "Ability to select the type of cell formatting (Text, Number, Date, etc) when exporting SolidWorks tables to Excel".


Just an FYI