FILTERXML of number value parses as datetimestamp in Excel2013

[sappollo]

Hi all,

Today I decided to use the FILTERXML for a new project. Within this project I can get counts per car manufacturer from external XML source. WEBSERVICE in combination with FILTERXML would be the perfect fit, I thought.

The setup was done quite easy. After investigation the results I discoverd some weird values. After some investigation I found out that every number extracted via XPath between 1900 and 9999 is automatically parsed as datetimestamp. Attached example excelsheet (without calling external webservice) to show the bug.

The only dirty workaround I found was to adjust the call to external webservice. I was able to multiply the count value with 10000. After the Xpath in Excel I divided the result again with 10000. This works for all value except value 0.

I haven't validated yet whether newer Excel version than Excel2013 still have this problem.

Any recommended solutions?

[#2]

@sappollo I'm encountering the same behaviour and bug in Excel 2016. Furthermore, I tried to utilize =NUMBERVALUE (=ZAHLENWERT in German), which is supposed to allow one to specify customized decimal-characters. However, it seems that =XMLFILTER (=XMLFILTERN in German) parses all numbers beforehand and, therefore, already interprets "1.8" as the first of August and outputs 44044.

This is a really annoying situation. For the mean time, I'm using the same workaround as @sappollo by multiplying everything by 10000 and only using integers in the XML.

[#3]

@amotzek 

Can't reproduce that on 365