I will try to give as much detail around this issue as I can. I have a folder of excel documents stored on a Microsoft teams site, about 20 identical trackers one for each team member. In this tracker is the following formula
=IF(ISBLANK(B5),NA(),B5&" "&TEXT(I5, "[$-0809]dd/mm/yy"))
It checks if there is content in B5, if not returns #N/A
, if so returns the content, adds a space, and the text format of a date in column I
.
For all but two of the users this formula works fine, however one team member based in Germany and one in Italy (might be related?) when they open their files returns #VALUE
. The current fix is that I (UK based) can simply open their trackers, click the formula and it calculates correctly, until the next time they open their trackers.
My understanding is that excel in a different region will automatically update formulas to the local language (i.e. the German user I would expect to see
=WENN(ISTLEER(B5);NV();B5&" "&TEXT(I5; "[$-0809]dd/mm/yy"))
However when I screen share with either user it still shows the English version of that formula, which leads me to believe thats causing the issue.
My question is does this issue appear to be caused by the regional setting of those two users, if not what might be the issue, and if so how could I make a cross-region friendly version of that formula.
TEXT
and/ordd/mm/yy
is the root of the issue. Maybe wrapping theTEXT
with anIFERROR
(and additional coding) could make it x-region, but I would think there would be something simpler.