3 Replies Latest reply: Dec 30, 2013 12:52 PM by Michael Ellerbeck RSS

    Export to EXCEL a Dimension DATE column like DATE format and not TEXT

      Hi

      I migrated to version 11.00 SR1 and seems that now, when we exported to Excel a Table with DATE columns , they are in text format(right click on cell->format cell-> is Text instead Date or Custom).

      it's strange because Expression DATE Columns in Excel are formatted as Date but Dimension Date Column are formatted as Text.

      in Dimension I tied to use functon DATE() but nothing changed.

      there is a solution?

      many thanks and best regards,

      Kinney

        • Re: Export to EXCEL a Dimension DATE column like DATE format and not TEXT
          Mohit Sharma

          Try to use Date(Date#(datefield,'DD-MM-YYYY'),'DD/MM/YYYY')

          here in this Datefield is my fieldname first format is that which comes in field then the 2nd format is that which i want now

          hope it helps you try to use this in dimension

          if your problem doesn't resolve then try it in script

          hope it helps...

            • Re: Export to EXCEL a Dimension DATE column like DATE format and not TEXT

              many thanks for your answer.

              unfortunatly this doesn't works.

              in the script i've already formast filed as Date.

              thanks again,

              kinney

                • Re: Export to EXCEL a Dimension DATE column like DATE format and not TEXT
                  Michael Ellerbeck

                  Hi Brian, I just fought with this for a bit. What worked for me was using a date() during the load script, but do not use a date() around the dimension. Once I removed the date() wrapped around the dimension it started exporting correctly. That being said I did contact support and they have a case titled "Chart is exported as text instead of numbers to Excel"

                  “The expression used in the chart generates a column with mixed formats, by applying individual formats from the indata Format field. Some values have decimals and other do not. This leads to the column not being defined as decimal numbers, but instead as a mixed column.

                    Mixed values are exported as Text, which happens in this case. This can also been seen when checking the Excel cells format value. This design decision was taken long ago and specifically to simply the export process and speed it up, the result of many customer complaints regarding the export to excel being extremely slow. So reverting to the old, abandoned method of exporting and that will not happen.

                  The solution is to have the same format for the entire column or manually reformat the fields in Excel.”

                   

                  So, if you have mixed values that would also throw it off I would suppose!