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:

Step1

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

Step2

 

Workaround

Standard solution for MS-Windows users

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

The textbox has a custom date format
Step4

The report is displayed with this custom format
Step5

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

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")

clip_image014

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

Step8

 

Reminder

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: