Newbie Questions

Re: XML Date integration into InfoPath form - whole number to date format

Jimmy — Mon, 17 Feb 2014 17:32:39 GMT

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.

Re: XML Date integration into InfoPath form - whole number to date format

Hilary Stoupa — Thu, 13 Feb 2014 19:49:20 GMT

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....

Re: XML Date integration into InfoPath form - whole number to date format

Paul Gohmann — Thu, 13 Feb 2014 17:57:36 GMT

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.

Re: XML Date integration into InfoPath form - whole number to date format

Hilary Stoupa — Thu, 13 Feb 2014 16:36:35 GMT

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

XML Date integration into InfoPath form - whole number to date format

Paul Gohmann — Thu, 13 Feb 2014 15:10:44 GMT

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