locked
Issue with Numbers as Date when exporting to Excel RRS feed

  • Question

  • Hello, I am having an issue when exporting a report to excel.

    I have a data value that is in the format ##-#### where in some cases is 11-9001 and in other cases it is 65-9328 or other variations. When displaying the report, the data comes out fine. However, when exporting to Excel or CSV formats, the data in the cases where it is 11-9001 the data exports as "Nov-01". Is there anyway I can format the data so this behavior doesn't happen. I've tried CStr(...) and .toString() but the behavior continues.

    Any help is appreciated. Thanks.

    Thursday, July 21, 2011 2:33 PM

Answers

  • Hi MAVSS,

    From your description, I can reproduce this issue on my local machine. This behavior is caused that Microsoft Excel automatically applies a built-in number format to a cell, based on the following criteria:
    • If a number contains a slash mark (/) or hyphen (-), it may be converted to a date format.
    • If a number contains a colon (:), or is followed by a space and the letter A or P, it may be converted to a time format.
    • If a number contains the letter E (in uppercase or lowercase letters; for example, 10e5), or the number contains more characters than can be displayed based on the column width and font, the number may be converted to scientific notation, or exponential, format.
    • If a number contains leading zeros, the leading zeros are dropped.

    For more information, please refer to: Text or number converted to unintended number format in Excel.

    In order to avoid this behavior, you can write an expression which includes a space and the field values. For example, we can set the expression like this:
    =” ” & Fields!MyField.Value

    If you have any more questions, please feel free to ask.

    Thanks,
    Bin Long

    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    • Marked as answer by Elvis Long Friday, July 29, 2011 4:56 AM
    Tuesday, July 26, 2011 7:46 AM