InfoPath Dev

I've searched these forums and Googled for a couple of hours yesterday and simply can't figure out how to resolve my issue. I'm taking an Excel file and exporting an XML file that is then used as a data connection in InfoPath. One of the fields in the XML is a date field which looks correct in the Excel cell as 1/31/2014 but it gets stored in XML as 42369. So, when I pull the date data, it is always showing as "42369" type of numeric value. I need it to show has 1/30/2014 (or something similar) in my InfoPath form. My thoughts are either I need to have a better XML Schema (which I really only started using 2 days ago) (perhaps some sort of "date declaration" in the schema??) or when I pull in the 42369, I need to somehow convert or format it to show 1/30/2014. I can't use any coding like VBS or C# in the solution (because I'm not a coder at all and I think it'll mess with form when I move it to being web based). I truly appreciate any help anyone can provide on this issue. Best regards, Paul

Sadly - 42369 is how Excel is actually storing that date. Have you run across this StackOverflow post that may be of help? http://stackoverflow.com/questions/7918347/exporting-xml-from-excel-and-keeping-date-format

Hi Hilary.. of all the people to respond, I was hoping it was you (no offense to the others :)) as I've read many of your other comments on dates and such. I have not ran into an issue with Stack Overflow. So, it sound like I'll have to figure out a way to store the date field in Excel as text perhaps and go that route?? Thanks for the help.

Might be worth a try - you could use a formula to return the year, month and day - you'll want it formatted like:

yyyy-MM-dd

You can also import a schema for Excel to use for the XML that will indicate a date field - but the above may be a simpler approach....

 I believe you should be able to convert the Excel date values to XML date values with this formula (in InfoPath)

 xdDate:AddDays("1899-12-31", ValueFromExcel)

 The value of xdDate:AddDays("1899-12-31", 42369) is 2016-01-01 (I believe the Excel value for 1/30/2014 is 41669).

If this is for display-only, you can have a Calculated Value control that shows this formula and that is formatted as a Date value, or you can use some Form Load rules in the form to do the conversion the first time a document is opened.