0

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.

Improve this question
2
  • Looking at what didn't translate between the two formula versions, maybeTEXT and/or dd/mm/yy is the root of the issue. Maybe wrapping the TEXT with an IFERROR (and additional coding) could make it x-region, but I would think there would be something simpler.
    – gns100
    Nov 24, 2021 at 17:36
  • I would like to suggest you let other users check the date format that they set for Windows system.
    – Emily
    Nov 25, 2021 at 7:49

1 Answer 1

Reset to default
1

I suspect the issue is the translation of custom numbers formats into local languages eg YY would be JJ auf Deutsch and AA en Francais.

You might have to parse your date into chunks to get this to work. In your formula:

=IF(ISBLANK(B5),NA(),B5&" "&TEXT(I5, "[$-0809]dd/mm/yy"))

Try replacing the TEXT bit with this:

TEXT(DAY(I5),"00")&"/"&TEXT(MONTH(I5),"00")&"/"&MOD(YEAR(I5),100)

You can simplify that if you don't always need two-digit day and month, or if you can use 4-digit years, for example. Worth a try at least.

Improve this answer
1
  • This is it, of sorts, its was the language issue of the date format, so for the German user I had to use 'TEXT(I5, "tt, mm, jj")', for tag and jahre! Interestingly I couldn't use those letters in my formulas for them, but screen sharing with control and editing as the end user worked, thank you.
    – Tim Wilkinson
    Nov 25, 2021 at 23:43

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .