#VALUE with DATEVALUE formula for some users
I have a situation whereby DATEVALUE formula works perfectly fine in excel on my PC and with another user it shows #VALUE .
The issue arised when a user tried to refresh the Analysis for office reports against the BEX query and the custom formula shows #VALUE for that user . For me on my Analysis for office, the formula works fine.
I understand that DATEVALUE works when the source cell is a text format and so is my source cell. For eg: Calendar day from BEX query in a cell is represented as '25.06.2015 and when I do =DATEVALUE(MID(Calendar_day(4,10)) , I get 42156 of course which I then set to display as Jun-15
But the same report when refreshed on another user PC shows #VALUE instead of Jun-15
We checked out language settings and both use English US.
Does anyone know why this difference in behaviour?