Introduction

Previously, we discussed formatting BI Publisher Dates in Excel. Formatting Date-Time fields is like date fields but require a few additional steps.

The problem stems from the lack of a Date-Time conversion formula in Excel. There is a Date conversion formula DATEVALUE() and a Time conversion formula TIMEVALUE(), but nothing that does the whole date-time conversion step in one, convenient function.

Our solution is to break the problem down into smaller, manageable chunks… just like most every other coding challenge.

XML Date Time Tag

PeopleSoft delivers the Date-time value as a text string in an XML tag. There isn’t additional formatting or XSD data in the XML file telling the receiving parser that this field is a Date-time. It’s just text that looks to a human like a Date Time combination. This example is from PSQuery.

Just bringing this field into an Excel template column defined with a “General” format will look great in the template and will look the same in the report. Different from just a date value, Excel looks at this whole string and doesn’t attempt to parse or format it. It leaves it as a big, ugly alpha-numeric string.

The format of this field may vary depending on the tool used to generate the XML from the source data.

The Problem

Our problems are at least two-fold:

it’s not easy for a human to look at a list of those long, complicated strings and comprehend their meaning.
Excel considers this a text field, not a Date-Time field. No meaningful analysis can be done on that field as is.

The Fix

Fix this by bringing the date-time field into the spreadsheet as a Text column. Then split the text of that column into two other columns, one for the date and the other for the time. Use Excel functions to convert those columns into actual date and time values. Then add those column values into a new third column defined in Excel as a Date-time format.

Once it’s in Excel Date-Time form, you can do any Excel Date-Time action needed whether it’s formatting to a specific pattern, date math, or other Excel Ninjitsu.

Using the Excel BI Publisher Desktop 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 date column to convert the text value of the incoming date to Excel Date format using the DATEVALUE() function using only the part of the text string that contains the date.

=DATEVALUE(LEFT(XDO_?XDOFIELD3?,10))

Notice here you are using Excel’s “Defined Name” for the date field from the original column. Not the column address. This can be found with the Field Browser or on the hidden XDO_METADATA worksheet.

5. Insert a new column and format as a Time cell

6. Create a formula in the new time column to convert the text value of the incoming date to Excel Date format using the TIMEVALUE() function using only the part of the text string that contains the time

=TIMEVALUE(MID(XDO_?XDOFIELD3?,12,8))

7. Add a third column and format the column to the desired Date-time format

8. Add the Date Column and the Time Column

=E2+F2

9. Hide the original Columns with the Text Date-time field and the work date and time fields.

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

Remember, this is an Excel spreadsheet. Most of your normal Excel Ninja tricks are available for manipulating the data to the format and display required. Play with the spreadsheets and use what is best for you. Theoretically, you can accomplish this same result using just one additional column and nesting multiple Excel functions.