Formatting Dates in BI Publisher Excel Templates

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.
Skip forward to sections:
0:00 Intro
0:28 Demonstrate the error
0:51 Problem is date is a string
1:27 Convert the string into an Excel Date
Production Notes:
A PT3 Production
Presenter: Randall Groncki
Camera Kid: Anjelica Groncki
Music: Vlad Gluschenko - Overseas
License: CC BY 3.0: creativecommons.org/licenses/...
/ @vladgluschenkomusic

[@sateeshbatchu5071]

How to convert Timestamp value (TEXT format) to Date format. Datevalue formula is truncating the time to 00:00:00.

[@PeopleToolsTechTips]

I'm going to get back with you by the end of the week on this one. I haven't yet used a timestamp in a BI Pub Report. My thought is that you have to first define your field in PeopleSoft as a date/time and make sure the time is correctly populated by the application. Then, use the same method in the RTF or Excel template, just allowing for the time instead of just date.
This may be a good idea for a quick short how to on the channel
Thanks!

[@mmmspaghetti]

Sateesh did you ever solve this problem? The alternative solution we're thinking of going with is changing the psquery select from DD/MM/YYYY HH:MM to YYYY/MM/DD HH:MM and not having another template column that transforms into an actual date format, this way we can have the time part and sorting will have the appearance of working.

[@sateeshkumarreddy117]

I’ve tried as it is but it’s not working.

[@PeopleToolsTechTips]

Just guessing
But make sure the column bringing in the date from the XML file is formatted as text.
Then use the excel date formula on that text field

[@sateeshkumarreddy117]

Yeah, I’m going through the same steps but still it’s not working. And is there any way can we do this with out creating a column.

[@RandallGroncki]

Not that I’m aware of.
You can start just by bringing in as text and displaying the next if it doesn’t matter if that field is type text and not date. The human will see it as a date