Monday, October 06, 2014

Dates in Excel files rendered from reports are displayed as plain numbers

Problem description

A report subscription is setup to send an email message on regular schedule. The report is included in the email as an attachment file rendered in excel format.

When MS-Windows users receive the email, they can open the excel file and dates will be shown in a normal date format:


When iPad users receive the email, they can open the excel file, but the dates are shown as numbers:




Standard solution for MS-Windows users

In the report design, the date field, e.g. ‘ReportDate’ is displayed as a regular field

The textbox has a custom date format

The report is displayed with this custom format

And the export to excel will show formatted date cells when opened with MS-Windows

However on the iPad the date in column H will be shown as a number.


Workaround for iPad users

In the report design do not use a custom format property; instead use an expression with the Format function to represent the date:
=Format(Fields!ReportDate.Value, "dd/MM/yyyy")


The result from the report subscription email attachment looks like this on the iPad:




With the workaround, the MS-Windows user will see the dates visualized correctly, but the cells are treated as plain text, not as dates. The cells are also left-aligned like any standard text.

No comments: