Excel TEXT formula error only for some regional users

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.

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.