LAMBDA function and its conversion options for locals / regions

LearningByDoing

LearningByDoing

New Member
Joined
Aug 17, 2023
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Sort by date Sort by votes
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)
 
Upvote 0
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.:
Excel Formula: 
=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:

Excel Formula: 
=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)
    )
)
 
Last edited:
Upvote 0
@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.
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question
#NAME? error in Excel for VBA Function
Replies
4
Views
2K
NdNoviceHlp
NdNoviceHlp
C
  • Question
Changing date format in Windows Regional Settings from VBA?
Replies
7
Views
7K
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,216,503
Messages
6,131,022
Members
449,616
Latest member
PsychoCube

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top