LAMBDA function and its conversion options for locals / regions

[LearningByDoing]

Hello
My questions relate to the LAMBDA function and its conversion options in relation to the locales / regions.

1. the creation of a LAMBDA according to the English locale (comma instead of semicolon, for lists { , } instead of { \ } etc.) causes problems when used in the European locale. For example, it is not possible to simply import from github without "correcting" everything "manually" or using "search / replace". This involves risks.
My question: Does anyone know or have a "risk-reduced" solution to solve this problem?

2. the use of a formatted date, for example "DDD MMM YYY" or variants thereof, is suboptimal if the LAMBDA function is created for different locales. This does not work and has to be changed manually for each locale. For example for the German variant TTTT MMMM JJJJ, for the Italian variant GGGG MMMM AAAA etc.).
Unfortunately, I am not yet aware of any "all-round solution" that handles this problem.
But what I do not know does not mean that this solution does not exist. Ergo, does anyone know of a way to deal with this problem?

Many thanks in advance
LearningByDoing

 

[Rijnsent]

1. I'm generally using Translator for that. There used to be a good excel add-in for it, but that isn't maintained anymore.
2. The best I've seen is some self-made VBA functions, using Range.NumberFormatLocal as an entry point, like so: How to prevent Excel to use the OS regional settings for date patterns in formulas - another good option is to leave to formatting to the excel cells, as that will be regional/local (and let LAMBDA only return the date value)

 

[Rijnsent]

Addition: you made me dive into LAMBDA, had the idea and with chatGPT and Excel-Translator I came up with the following:

• with some testing on my Dutch excel (ddd mmm jjjj is my date string), I tried all 26 letters of the alphabet combined with the TEXT function, so "j" in my case shows a 2-digit year and "d" shows the day. "n" throws a #N/A error. So basically, I'd like to loop the alphabet and see where 24 is the answer to find my character for "year".
• =CHAR(SEQUENCE(26,1,97,1)) -> SEQUENCE is a newish excel function, this basically gives you a list of all characters from a to z
• I'll take a fixed date, 45408 (26 april 2024) and check for the right outcomes: =IFERROR(TEXT(45408,CHAR(SEQUENCE(26,1,97,1))),"") -> every non-relevant letter returns itself (so "x" will return "x"), but the relevant ones return the right number: in my Dutch excel "j" returns 20.
• next, this needs to be wrapped in a INDEX/MATCH and a LAMBDA (thanks, chatGPT + Excel Translator), e.g.:

=LAMBDA(anything,
    LET(
        dt,45408,
        chars, SEQUENCE(26, 1, 97, 1),
        charText, CHAR(chars),
        yearMatch, INDEX(charText,MATCH("24",IFERROR(TEXT(dt,charText),""),0)),
        yearMatch
    )
)

• create a name in Excel where you enter that formula as the value and name it e.g. "year_char"
• in a sheet, in a cell, enter =year_char(0) (what you put into the formula is not relevant) and it should return the "year" character ("j" in my Excel)

So, if you want to expand that with month and day, this LAMBDA should return the localized YYYY-MM-DD string that you can use in your TEXT functions:

=LAMBDA(anything,
    LET(
        dt,45408,
        chars, SEQUENCE(26, 1, 97, 1),
        charText, CHAR(chars),
        yearMatch, INDEX(charText,MATCH("24",IFERROR(TEXT(dt,charText),""),0)),
        monthMatch, INDEX(charText,MATCH("4",IFERROR(TEXT(dt,charText),""),0)),
        dayMatch, INDEX(charText,MATCH("26",IFERROR(TEXT(dt,charText),""),0)),
        REPT(yearMatch,4) & "-" & REPT(monthMatch,2) & "-" & REPT(dayMatch,2)
    )
)

 

[LearningByDoing]

@Rijnsent
thank you for the detailed explanation. I will take a closer look at your thoughts over the next week, as I hope to have more time then.

What concerns me regarding conversion of LAMBDAs is that there are no conversion problems in locales / regions when the LAMBDA is provided in the form of a workbook. But I don't have the detailed knowledge for this.

 

[Rijnsent]

I added my LAMBDA function to the collection on this forum: LOCAL_YMD -> the response from XLambda gives a way more elegant formula. You can basically add that LAMBDA to your workbook and the function will always give back the local version for yyyy-mm-dd (so in German that would be jjjj-mm-tt) which you could than use in your formulas and should work in all language environments.

 

[LearningByDoing]

@Rijnsent
... I read a lot and take a look at your LAMBDA .

and then I read the following thread

... I don't know when it will be ready, but it could solve some difficulties.