LOCAL_YMD

=LOCAL_YMD(0)
Returns the localized version of YYYY-MM-DD (for a Dutch Windows that's JJJJ-MM-DD, etc.), can be used in TEXT functions.
Rijnsent

Rijnsent

Well-known Member
Joined
Oct 17, 2005
Messages
1,460
Office Version
  1. 365
Platform
  1. Windows
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)
    )
)

LOCAL_YMD
ABC
1
2=LOCAL_YMD(0)
3yyyy-mm-dd
4
Sheet1
Cell Formulas
RangeFormula
B2B2=FORMULATEXT(B3)
B3B3=LOCAL_YMD(0)
Lambda Functions
NameFormula
LOCAL_YMD=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) ) )


Comes from this thread: LAMBDA function and its conversion options for locals / regions
 
Last edited by a moderator:
Upvote 0
Super cool! ✌️, or this one:
YMD() Year Month Day, argument free function
Excel Formula: 
=LAMBDA(
    LET(
        l, CHAR(SEQUENCE(26, , 97)),
        TEXTJOIN("-", , REPT(INDEX(l, XMATCH({3, 2, 1}, --TEXT(1128, l))), {4, 2, 2}))
    )
)
Book1
ABC
1
2=YMD()
3yyyy-mm-dd
4
5
Sheet1
Cell Formulas
RangeFormula
B2B2=FORMULATEXT(B3)
B3B3=YMD()
 
Cool, my take-aways:
- One can make a parameter-free LABMDA function
- 1128 is 1st of February 2003 (my example is my last birthday :-p)
- L is a terrible letter to use as a variable in a LAMBDA, as it is hard to distinguish from a 1 ;-)
- my version is easier to read (especially for a noob like me), but yours is waaaaay more elegant & efficient
 
A minor improvement on your version, as apparently for some regional settings you'd need capitals (found that in a SO post). I'm puzzling around a bit with hours, minutes and seconds, but that's a bit harder as for me (NL settings), minutes are defined as mm, the same as months, but only if they follow hours (so hh:mm or hh-mm) they get recognized as minutes, otherwise they display as months.

Excel Formula: 
=LAMBDA(
    LET(
        z, CHAR(VSTACK(SEQUENCE(26, , 97),SEQUENCE(26, , 65))),
        TEXTJOIN("-", , REPT(INDEX(z, XMATCH({3, 2, 1}, --TEXT(1128, z))), {4, 2, 2}))
    )
)
 
Super Cool challenge; had no idea that could be regional formats that cannot accept as date/time formats both lower/upper case forms of respective letters.
So, we can solve this, plus identifying "h" "m" "s" formats, all in one function YMDHMS, based on a quite simple concept:
Hope I`ve managed to make it easy to follow and you'll like it. 😉
Book1
ABCDEFGHIJKLMNOPQRSTU
1YMDHMS concept's highlights:
2 - will check date and time formats for all lower/upper case letters
3 - date used (smallest possible with different digits) 3-feb-1901 <=> 400 numeric date value
4 - to check time format will use "absolute" time format "[..]"
5
6=TEXT(400,"[h]")
79600 => as values to match, apart from 1,2,3 for y,m,d ,will be also these:
8 9600, 576000, 34560000 that will corespond to [h],[m],[ s] formats
9=TEXT(400,"[m]")
10576000→( this number will match for "[m]" but not for "m",
11and this is how we can differentiate them even if they use same letter)
12=TEXT(400,"[s]")
1334560000
14
15 - if date format accepts both lower and upper will have same values twice
16=--TEXT(400,B17:B22)
17d3 => will have 2 different index values => we can both match them
18D3using first to last and last to first, XMATCH search_mode arguments 1 and -1
19m2
20M2=XMATCH({1,2,3},D17#,,{1;-1})=INDEX(B17:B22,F21#)=REPT(K21#,{4,2,2})
21y1531ymdyyyymmdd
22Y1642YMDYYYYMMDD
23
24=TEXTJOIN({"-","-","|"},,O21#)
25yyyy-mm-dd|YYYY-MM-DD
26
27If for example, in some regional format, only capital "Y" works , the results will look like this
28d3
29D3=XMATCH({1,2,3},D28:D33,,{1;-1})=INDEX(B28:B33,F30#)=REPT(K30#,{4,2,2})
30m2631YmdYYYYmmdd
31M2642YMDYYYYMMDD
32y#VALUE!
33Y1=TEXTJOIN({"-","-","|"},,O30#)
34YYYY-mm-dd|YYYY-MM-DD
35
36!!!!!like this is easy to spot that for year only capital letter is accepted
37
Sheet1
Cell Formulas
RangeFormula
C6,O33,F29,K29,O29,O24,F20,K20,O20,D16,C12,C9C6=FORMULATEXT(C7)
C7C7=TEXT(400,"[h]")
C10C10=TEXT(400,"[m]")
C13C13=TEXT(400,"[s]")
D17:D22D17=--TEXT(400,B17:B22)
F21:H22F21=XMATCH({1,2,3},D17#,,{1;-1})
K21:M22K21=INDEX(B17:B22,F21#)
O21:Q22,O30:Q31O21=REPT(K21#,{4,2,2})
O25,O34O25=TEXTJOIN({"-","-","|"},,O21#)
F30:H31F30=XMATCH({1,2,3},D28:D33,,{1;-1})
K30:M31K30=INDEX(B28:B33,F30#)
D32D32=""+1
Dynamic array formulas.
 
Book1
ABCDEFGHIJKLMNOPQRST
1Concept step by step
2create alternating lower/upper alphabet + stack "[ ]" format
3
4=CHAR(SEQUENCE(26)+{96,64})=VSTACK(E6#,"[ "&E6#&"]")
5↓↓=TOCOL(B6#)↓↓=--TEXT(400,G6#)=XMATCH({1,2,3,9600,576000,34560000},I6#,,{1;-1})
6aAaa#VALUE!49257677789
7bBAA#VALUE!50268687890
8cCbb44
9dDBB#VALUE!=INDEX(G6#,K6#)
10eEcc#VALUE!ymd[ h][ m][ s]
11fFCC#VALUE!YMD[ H][ M][ S]
12gGdd3
13hHDD3trick:
14iIee1901because we stacked the simple "letters" to" [letters] " there are 52 index positions between
15jJEE#VALUE!"letter" and "[letter]", (26 lower+26 upper) so we can use MOD to return same indexes
16kKff#VALUE!
17lLFF#VALUE!=MOD(K6#-1,52)+1
18mMgg#VALUE!49257152537
19nNGG#VALUE!50268162638
20oOhh0
21pPHH0=INDEX(G6#,K18#)
22qQii#VALUE!ymdhms
23rRII#VALUE!YMDHMS
24sSjj#VALUE!
25tTJJ#VALUE!=REPT(K22#,{4,2,2,2,2,2})
26uUkk#VALUE!yyyymmddhhmmss
27vVKK#VALUE!YYYYMMDDHHMMSS
28wWll#VALUE!
29xXLL#VALUE!=TEXTJOIN({"-","-"," ",":",":","|"},,K26#)
30yYmm2yyyy-mm-dd hh:mm:ss|YYYY-MM-DD HH:MM:SS
31zZMM2
32nn#VALUE!
33NN#VALUE!
34oo#VALUE!
35OO#VALUE!
36pp#VALUE!
37PP#VALUE!
38qq#VALUE!
39QQ#VALUE!
40rr#VALUE!
41RR#VALUE!
42ss0
43SS0
44tt#VALUE!
45TT#VALUE!
46uu#VALUE!
47UU#VALUE!
48vv#VALUE!
49VV#VALUE!
50ww#VALUE!
51WW#VALUE!
52xx#VALUE!
53XX#VALUE!
54yy1
55YY1
56zz#VALUE!
57ZZ#VALUE!
58[ a]#VALUE!
59[ A]#VALUE!
60[ b]#VALUE!
61[ B]#VALUE!
62[ c]#VALUE!
63[ C]#VALUE!
64[ d]#VALUE!
65[ D]#VALUE!
66[ e]#VALUE!
67[ E]#VALUE!
68[ f]#VALUE!
69[ F]#VALUE!
70[ g]#VALUE!
71[ G]#VALUE!
72[ h]9600
73[ H]9600
74[ i]#VALUE!
75[ I]#VALUE!
76[ j]#VALUE!
77[ J]#VALUE!
78[ k]#VALUE!
79[ K]#VALUE!
80[ l]#VALUE!
81[ L]#VALUE!
82[ m]576000
83[ M]576000
84[ n]#VALUE!
85[ N]#VALUE!
86[ o]#VALUE!
87[ O]#VALUE!
88[ p]#VALUE!
89[ P]#VALUE!
90[ q]#VALUE!
91[ Q]#VALUE!
92[ r]#VALUE!
93[ R]#VALUE!
94[ s]34560000
95[ S]34560000
96[ t]#VALUE!
97[ T]#VALUE!
98[ u]#VALUE!
99[ U]#VALUE!
100[ v]#VALUE!
101[ V]#VALUE!
102[ w]#VALUE!
103[ W]#VALUE!
104[ x]#VALUE!
105[ X]#VALUE!
106[ y]#VALUE!
107[ Y]#VALUE!
108[ z]#VALUE!
109[ Z]#VALUE!
110
Sheet2
Cell Formulas
RangeFormula
B4,G4B4=FORMULATEXT(B6)
E5,K29,K25,K21,K17,K9,I5,K5E5=FORMULATEXT(E6)
B6:C31B6=CHAR(SEQUENCE(26)+{96,64})
E6:E57E6=TOCOL(B6#)
G6:G109G6=VSTACK(E6#,"[ "&E6#&"]")
I6:I109I6=--TEXT(400,G6#)
K6:P7K6=XMATCH({1,2,3,9600,576000,34560000},I6#,,{1;-1})
K10:P11K10=INDEX(G6#,K6#)
K18:P19K18=MOD(K6#-1,52)+1
K22:P23K22=INDEX(G6#,K18#)
K26:P27K26=REPT(K22#,{4,2,2,2,2,2})
K30K30=TEXTJOIN({"-","-"," ",":",":","|"},,K26#)
Dynamic array formulas.
 
And all the entire concept in one tiny function:
(Will be cool if you'll post what the function is returning on your spreadsheet (if you have it in Dutch). Or any other user, in any other language.)
YMDHMS() argument free function
Excel Formula: 
=LAMBDA(
    LET(
        z, TOCOL(CHAR(SEQUENCE(26) + {96, 64})),
        TEXTJOIN(
            {"-", "-", " ", ":", ":", "|"},
            ,
            REPT(
                INDEX(z, MOD(XMATCH({1, 2, 3, 9600, 576000, 34560000}, --TEXT(400, VSTACK(z, "[" & z & "]")), , {1; -1}) - 1, 52) + 1),
                {4, 2, 2, 2, 2, 2}
            )
        )
    )
)
Book1
ABCDEF
1English
2=YMDHMS()
3yyyy-mm-dd hh:mm:ss|YYYY-MM-DD HH:MM:SS
4
5
Sheet3
Cell Formulas
RangeFormula
B2B2=FORMULATEXT(B3)
B3B3=YMDHMS()
 
First of all: that is indeed impressively elegant.

Somehow my Excel won't accept the second array element of your functions, so I get a #N/A answer.
It basically starts off with the SEQUENCE(26) + {96, 64}) formula, that simply will give a one-column answer: a, B, and than 24 times #N/A, not your two column answer. I tried both the formula and entering it with CTRL+SHIFT+ENTER. For your XMATCH formula, I have the same issue: =X.VERGELIJKEN({1;2;3;9600;576000;34560000};I5#;;1) works (that's my Dutch XMATCH in my local settings ;-)), but when I replace the last element with {1;-1} the formula doesn't work anymore. Am working on rebuilding your function a bit less elegant, but working :-p.
 
I think it is about separators, in my syntax "," represents columns. and ";" rows. In yours is the other way around, you should use semicolons ";" for columns.
Anyhow, to make the function syntax free in this respect, I will replace all constant arrays with hstack/vstack accordingly, therefore try this one:
YMDHMS() argument free and syntax free function
Excel Formula: 
=LAMBDA(
    LET(
        z, TOCOL(CHAR(SEQUENCE(26) + HSTACK(96, 64))),
        TEXTJOIN(
            HSTACK("-", "-", " ", ":", ":", "|"),
            ,
            REPT(
                INDEX(z, MOD(XMATCH(HSTACK(1, 2, 3, 9600, 576000, 34560000), --TEXT(400, VSTACK(z, "[" & z & "]")), , VSTACK(1, -1)) - 1, 52) + 1),
                HSTACK(4, 2, 2, 2, 2, 2)
            )
        )
    )
)
YMD.xlsx
ABCDEF
1English
2=YMDHMS()
3yyyy-mm-dd hh:mm:ss|YYYY-MM-DD HH:MM:SS
4
Sheet3
Cell Formulas
RangeFormula
B2B2=FORMULATEXT(B3)
B3B3=YMDHMS()
 
So this is my Dutch/European translation of your function, works like a charm :). Those arrays in functions are indeed a bit of a nuisance, I've tried various versions, but I think you HSTACK/VSTACK solution is more universal :
Excel Formula: 
=LAMBDA(
    LET(
        z; NAAR.KOLOM(TEKEN(REEKS(26) + HOR.STAPELEN(96; 64)));
        TEKST.COMBINEREN(
            HOR.STAPELEN("-"; "-"; " "; ":"; ":"; "|");
            ;
            HERHALING(
                INDEX(z; REST(X.VERGELIJKEN(HOR.STAPELEN(1; 2; 3; 9600; 576000; 34560000); --TEKST(400; VERT.STAPELEN(z; "[" & z & "]")); ; VERT.STAPELEN(1; -1)) - 1; 52) + 1);
                HOR.STAPELEN(4; 2; 2; 2; 2; 2)
            )
        )
    )
)

Outcome:
jjjj-mm-dd uu:mm:ss|JJJJ-MM-DD UU:MM:SS
 
You must log in or register to reply here.

Forum statistics

Threads
1,224,820
Messages
6,181,159
Members
453,021
Latest member
Justyna P

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