Date formatting interferes with formulas

[dotsent]

Hi! I created an Excel tool that utilizes basic DATE to TEXT formula to get a certain output format:

TEXT(TODAY(),"yyyymmdd")

I had to hand the tool over, but it doesn't work in the other PC anymore (some bogus output). Both are Win10 + Office365. Main difference - while my PC regional settings are Estonian, the other PC has Finnish setup. All the regional settings are in either EST or FIN, though both computers run Excel in English.

The way this formula runs in another PC is adjusting formula to:

TEXT(TODAY(),"vvvvkkpp")

This is the same formula, however in Finnish (v = year, k = month, p = day). With that being said, "yyyymmdd" clearly is NOT Estonian either, but it works regardless. I don't want to change the formula into Finnish format as it wouldn't run on my PC anymore. Is there a way to overcome this without destroying either PC-s regional settings? I think backup plan is switch all date manipulation to VBA Format function, but perhaps there's an easier way to retain the formulas? Must admit, was quite surprised to see regional settings shift formula syntax like that...

Any chance to run the same original formula with a different Windows regional setup?

 

[BSALV]

you need to know the language code
for example Specifying a Language for the TEXT Function

Map2
	C	D
29	Finland	pe 15/huhtikuu/2022
30	Estonia	R 15/aprill/2022
Blad1

Cell Formulas
Range	Formula
D29	D29	=TEXT(TODAY(),"[$-040B]ddd dd/mmmm/jjjj")
D30	D30	=TEXT(TODAY(),"[$-0425]ddd dd/mmmm/jjjj")

 

[BSALV]

Finland vs Estonia
when i thought about it, that wasn't your question.
You want to show with 1 formula the same day

Finland_Estonia.xlsm
	A
11	20220415
12	vrijdag 15 april 2022
Blad2

Cell Formulas
Range	Formula
A11	A11	=TEXT(TODAY(),DateLocal_YYYYMMDD)
A12	A12	=TEXT(TODAY(),DateLocal_long)

the 2 defined names for the long or the yyyymmdd dateformat are
english =IF(ISNUMBER(TEXT(TODAY(),"YYYY")),"dddd dd mmmm yyyy",IF(ISNUMBER(TEXT(TODAY(),"vvvv")),"pppp pp kkkk vvvv","dddd dd mmmm jjjj"))
Finnish =JOS(ONLUKU(TEKSTI(TÄMÄ.PÄIVÄ();"YYYY"));"dddd dd mmmm yyyy";JOS(ONLUKU(TEKSTI(TÄMÄ.PÄIVÄ();"vvvv"));"pppp pp kkkk vvvv";"dddd dd mmmm jjjj"))

english =IF(ISNUMBER(TEXT(TODAY();"YYYY"));"yyyymmss";IF(ISNUMBER(TEXT(TODAY();"vvvv"));"vvvvkkpp";"jjjjmmdd"))
Finnish =JOS(ONLUKU(TEKSTI(TÄMÄ.PÄIVÄ();"YYYY"));"yyyymmss";JOS(ONLUKU(TEKSTI(TÄMÄ.PÄIVÄ();"vvvv"));"vvvvkkpp";"jjjjmmdd"))

 

[dotsent]

you need to know the language code
for example Specifying a Language for the TEXT Function

Map2
	C	D
29	Finland	pe 15/huhtikuu/2022
30	Estonia	R 15/aprill/2022
Blad1

Cell Formulas
Range	Formula
D29	D29	=TEXT(TODAY(),"[$-040B]ddd dd/mmmm/jjjj")
D30	D30	=TEXT(TODAY(),"[$-0425]ddd dd/mmmm/jjjj")

Hi BSALV and thanks for taking the time to respond. The link was an interesting read, but you are right - I'm not looking to change the output format of the formula, but rather have a formula that would work, regardless if the local regional settings are EST or FIN. So it's an input problem, not output.

 

[dotsent]

Finland vs Estonia
when i thought about it, that wasn't your question.
You want to show with 1 formula the same day

Finland_Estonia.xlsm
	A
11	20220415
12	vrijdag 15 april 2022
Blad2

Cell Formulas
Range	Formula
A11	A11	=TEXT(TODAY(),DateLocal_YYYYMMDD)
A12	A12	=TEXT(TODAY(),DateLocal_long)

the 2 defined names for the long or the yyyymmdd dateformat are
english =IF(ISNUMBER(TEXT(TODAY(),"YYYY")),"dddd dd mmmm yyyy",IF(ISNUMBER(TEXT(TODAY(),"vvvv")),"pppp pp kkkk vvvv","dddd dd mmmm jjjj"))
Finnish =JOS(ONLUKU(TEKSTI(TÄMÄ.PÄIVÄ();"YYYY"));"dddd dd mmmm yyyy";JOS(ONLUKU(TEKSTI(TÄMÄ.PÄIVÄ();"vvvv"));"pppp pp kkkk vvvv";"dddd dd mmmm jjjj"))

english =IF(ISNUMBER(TEXT(TODAY();"YYYY"));"yyyymmss";IF(ISNUMBER(TEXT(TODAY();"vvvv"));"vvvvkkpp";"jjjjmmdd"))
Finnish =JOS(ONLUKU(TEKSTI(TÄMÄ.PÄIVÄ();"YYYY"));"yyyymmss";JOS(ONLUKU(TEKSTI(TÄMÄ.PÄIVÄ();"vvvv"));"vvvvkkpp";"jjjjmmdd"))

Thanks again. I promised myself "not to go for some IFS-nonsense" and implement VBA in that case but I try to wrap my head around it first. It's Office365, so I can use IFS-function as well, but are we testing if one input format ("yyyymmdd") gives an error and if it does, we use another input ("vvvvkkpp")? It wasn't an error response (#N/A), but something clearly wrongly formatted.

What is interesting though - while there seem to exist translations for native formula functions in Finnish, the PC worked with English formulas, so the function name itself didn't look to be a problem - just the date format.

Also, when formatting cells - custom formatting didn't suggest English d/m/y combinations, but it was in Finnish (p/k/v).