Introduction

Adding dates from an XML File into an Excel template usually starts with seeing the wrong data in every row.   BI Publisher seems to be ignoring the XML Date value and just printing the generic field value from the template.

Properly handling the dates between the XML File and Excel is actually quite easy once you understand what is happening and how to correct the issue.

The Problem

The problem is caused when PeopleSoft generates the XML file with the date value as a Character type string.  It may look like a date to you, but there is nothing different than any other text field such as a name or description.

Excel further exasperates the situation by assigning the field to a “General” formatted cell type.   It’s attempting to do something meaningful with the value.   It’s not sure if it’s a math formula, a phone number, a serial number, or some other random text string.   The BI Pub engine resolves the issue by just throwing in a default label value into the field (“1/01/1900”).

The Fix

Fix this by allowing the date value to come into the spreadsheet as a text value.  Then using another column for Excel to convert that text into an actual Excel Date.   Once it’s in Excel Date form, you can do any Excel Date action needed whether it’s formatting to a specific pattern, date math, or some of the function.

1. Using the BI Publisher Excel Template helper, add the date field to your Excel template as normal.
2. Reformat that Excel cell/column to type TEXT.

3. Insert a new column and format as a date cell

4. Create a formula in the new column to convert the text value of the incoming date to Excel Date format using the DATEVALUE() function.

=DATEVALUE(XDO_?XDOFIELD3?)

5. Hide the original Column with the date field formatted as text

6. Now the date from the XML file will appear as the correct date in your report.   Since it is now an Excel Date format, you can use other Excel functions such as date math

Oracle Documentation – Formatting dates in excel templates