CODEWITHSUNDEEP
excelreporting-services
mason
mason

Reputation: 32694

How do I change data type of cells in SSRS report exported to Excel?

I have an SSRS report that is exporting data to Excel (xlsx). I have a column of data that contains dates. In the resulting XLSX file, I need the data type to be Short Date, but it comes out as General.

picture showing Excel data types

I tried changing the format of my placeholder in SSRS designer (being edited with VS 2015) to a date format, but the resulting Excel file still has it as General type.

picture showing number format in SSRS Designer

The relevant XML from the RDLC is shown below.

<TablixCell>
  <CellContents>
    <Textbox Name="AdmitDate">
      <CanGrow>true</CanGrow>
      <KeepTogether>true</KeepTogether>
      <Paragraphs>
        <Paragraph>
          <TextRuns>
            <TextRun>
              <Value>=Fields!AdmitDate.Value</Value>
              <Style>
                <FontSize>8pt</FontSize>
                <Format>MM/dd/yyyy</Format>
                <Color>#4d4d4d</Color>
              </Style>
            </TextRun>
          </TextRuns>
        </Paragraph>
      </Paragraphs>
      <rd:DefaultName>AdmitDate</rd:DefaultName>
    </Textbox>
  </CellContents>
</TablixCell>

How can I get SSRS to set the data type for the cells, assuming it's possible?

Upvotes: 2

Views: 2991

Answers (1)

Hannover Fist
Hannover Fist

Reputation: 10860

I am doing a similar date field in some of my reports and they do (surprisingly) export as a Custom Date.

enter image description here

I'm thinking your field may not be recognized by SSRS as a date field. Notice that my date is 3/15/2016 in Excel's function bar while yours has the leading 0 in the month - 08/25/2008.

Have you tried wrapping it in a CDATE function in your expression?

=CDATE(Fields!AdmitDate.Value)

Upvotes: 3

Related Questions