Xlambda
Well-known Member
- Joined
- Mar 8, 2021
- Messages
- 860
- Office Version
- 365
- Platform
- Windows
ADATE replaces only the dates column values of an array with other data types formats, leaving the rest of the array intact.
t (type) argument options : "f" , first day of month ; "e" , end of month ; "m" , month ; "y" , year ; "q" , quarter ; "od" , only date value ; "om" , only month ; "oq" , only quarter
Other functions on minisheet ADVFLT , ARRANGE , ACOMBINE
t (type) argument options : "f" , first day of month ; "e" , end of month ; "m" , month ; "y" , year ; "q" , quarter ; "od" , only date value ; "om" , only month ; "oq" , only quarter
Other functions on minisheet ADVFLT , ARRANGE , ACOMBINE
Excel Formula:
=LAMBDA(a,t,cl,
LET(c,MAX(1,cl),l,COLUMNS(a),sl,SEQUENCE(,l),
d,DAY(a),m,MONTH(a),y,YEAR(a),r,y&"-",
x,SWITCH(t,"m",TEXT(a,"yyy-mm-mmm"),"y",TEXT(a,"yyy"),"e",EOMONTH(+a,0),"f",EOMONTH(+a,-1)+1,"q",IFS(m<=3,r&"Q1",
m<=6,r&"Q2",m<=9,r&"Q3",m<=12,r&"Q4"),"od",d,"om",TEXT(a,"mm-mmm"),"oq",IFS(m<=3,"Q1",m<=6,"Q2",m<=9,"Q3",m<=12,"Q4")),
IF(OR(t={"od","m","y","q","e","f","om","oq"}),IF(sl=c,x,a),"check data")
)
)
LAMBDA 7.0.xlsx | ||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA | AB | AC | AD | AE | AF | AG | AH | AI | AJ | |||
1 | =RANDARRAY(20,,"1-01-19",TODAY(),1) | sample 2 | filter btw Mar-2019 and Mar 2020 | |||||||||||||||||||||||||||||||||||
2 | rnd dates generator | sample 1 | =ADATE(C3:E22,"f",) | =ADATE(C3:E22,"e",) | =ADATE(C3:E22,"m",) | =ADATE(C3:E22,"y",) | =ARRANGE(C3:E22,{3,1}) | =ADATE(W3#,"m",2) | =ADVFLT(AA3#,{2,2},{">=2019-03-mar","<=2020-03-mar"},,) | |||||||||||||||||||||||||||||
3 | 30-04-20 | 05-05-19 | B | x | 01-05-19 | B | x | 31-05-19 | B | x | 2019-05-May | B | x | 2019 | B | x | x | 43590 | B | x | 2019-05-May | B | x | 2019-05-May | B | |||||||||||||
4 | 01-09-19 | 27-02-21 | A | x | 01-02-21 | A | x | 28-02-21 | A | x | 2021-02-Feb | A | x | 2021 | A | x | x | 44254 | A | x | 2021-02-Feb | A | y | 2019-12-Dec | A | |||||||||||||
5 | 12-03-21 | 07-03-21 | B | y | 01-03-21 | B | y | 31-03-21 | B | y | 2021-03-Mar | B | y | 2021 | B | y | y | 44262 | B | y | 2021-03-Mar | B | x | 2019-05-May | B | |||||||||||||
6 | 30-04-21 | 19-12-19 | A | y | 01-12-19 | A | y | 31-12-19 | A | y | 2019-12-Dec | A | y | 2019 | A | y | y | 43818 | A | y | 2019-12-Dec | A | y | 2020-02-Feb | A | |||||||||||||
7 | 21-01-20 | 05-08-20 | B | y | 01-08-20 | B | y | 31-08-20 | B | y | 2020-08-Aug | B | y | 2020 | B | y | y | 44048 | B | y | 2020-08-Aug | B | y | 2020-02-Feb | A | |||||||||||||
8 | 20-07-20 | 08-05-19 | B | x | 01-05-19 | B | x | 31-05-19 | B | x | 2019-05-May | B | x | 2019 | B | x | x | 43593 | B | x | 2019-05-May | B | y | 2020-03-Mar | A | |||||||||||||
9 | 15-09-19 | 06-02-20 | A | y | 01-02-20 | A | y | 29-02-20 | A | y | 2020-02-Feb | A | y | 2020 | A | y | y | 43867 | A | y | 2020-02-Feb | A | x | 2019-06-Jun | A | |||||||||||||
10 | 21-06-20 | 09-02-20 | A | y | 01-02-20 | A | y | 29-02-20 | A | y | 2020-02-Feb | A | y | 2020 | A | y | y | 43870 | A | y | 2020-02-Feb | A | ||||||||||||||||
11 | 09-05-19 | 09-01-19 | B | x | 01-01-19 | B | x | 31-01-19 | B | x | 2019-01-Jan | B | x | 2019 | B | x | x | 43474 | B | x | 2019-01-Jan | B | advanced filter options are endless | |||||||||||||||
12 | 30-08-19 | 24-11-20 | A | x | 01-11-20 | A | x | 30-11-20 | A | x | 2020-11-Nov | A | x | 2020 | A | x | x | 44159 | A | x | 2020-11-Nov | A | filter all values except year 2020 | |||||||||||||||
13 | 02-09-20 | 28-04-20 | B | y | 01-04-20 | B | y | 30-04-20 | B | y | 2020-04-Apr | B | y | 2020 | B | y | y | 43949 | B | y | 2020-04-Apr | B | =ADVFLT(AA3#,,,{2,2},{"<=2019-12-dec",">2020-12-dec"}) | |||||||||||||||
14 | 02-07-20 | 01-04-21 | A | x | 01-04-21 | A | x | 30-04-21 | A | x | 2021-04-Apr | A | x | 2021 | A | x | x | 44287 | A | x | 2021-04-Apr | A | x | 2019-05-May | B | |||||||||||||
15 | 02-02-20 | 16-04-21 | B | x | 01-04-21 | B | x | 30-04-21 | B | x | 2021-04-Apr | B | x | 2021 | B | x | x | 44302 | B | x | 2021-04-Apr | B | x | 2021-02-Feb | A | |||||||||||||
16 | 21-05-19 | 29-03-20 | A | y | 01-03-20 | A | y | 31-03-20 | A | y | 2020-03-Mar | A | y | 2020 | A | y | y | 43919 | A | y | 2020-03-Mar | A | y | 2021-03-Mar | B | |||||||||||||
17 | 24-02-19 | 16-02-21 | A | y | 01-02-21 | A | y | 28-02-21 | A | y | 2021-02-Feb | A | y | 2021 | A | y | y | 44243 | A | y | 2021-02-Feb | A | y | 2019-12-Dec | A | |||||||||||||
18 | 06-01-19 | 21-03-21 | B | x | 01-03-21 | B | x | 31-03-21 | B | x | 2021-03-Mar | B | x | 2021 | B | x | x | 44276 | B | x | 2021-03-Mar | B | x | 2019-05-May | B | |||||||||||||
19 | 30-08-20 | 18-06-19 | A | x | 01-06-19 | A | x | 30-06-19 | A | x | 2019-06-Jun | A | x | 2019 | A | x | x | 43634 | A | x | 2019-06-Jun | A | x | 2019-01-Jan | B | |||||||||||||
20 | 13-02-20 | 23-04-20 | A | x | 01-04-20 | A | x | 30-04-20 | A | x | 2020-04-Apr | A | x | 2020 | A | x | x | 43944 | A | x | 2020-04-Apr | A | x | 2021-04-Apr | A | |||||||||||||
21 | 21-04-20 | 01-01-19 | B | y | 01-01-19 | B | y | 31-01-19 | B | y | 2019-01-Jan | B | y | 2019 | B | y | y | 43466 | B | y | 2019-01-Jan | B | x | 2021-04-Apr | B | |||||||||||||
22 | 28-07-19 | 08-10-20 | A | y | 01-10-20 | A | y | 31-10-20 | A | y | 2020-10-Oct | A | y | 2020 | A | y | y | 44112 | A | y | 2020-10-Oct | A | y | 2021-02-Feb | A | |||||||||||||
23 | x | 2021-03-Mar | B | |||||||||||||||||||||||||||||||||||
24 | =ADATE(C3:E22,"q",) | =ADATE(C3:E22,"od",) | =ADATE(C3:E22,"om",) | =ADATE(C3:E22,"oq",) | =ADATE(W3#,"oq",2) | filter only Q1 or Q4 values all time | x | 2019-06-Jun | A | |||||||||||||||||||||||||||||
25 | t (type) argument | data type | format | 2019-Q2 | B | x | 5 | B | x | 05-May | B | x | Q2 | B | x | x | Q2 | B | =ADVFLT(W25#,,,{2,2},{"q1","q4"}) | y | 2019-01-Jan | B | ||||||||||||||||
26 | f | first of month | date | 2021-Q1 | A | x | 27 | A | x | 02-Feb | A | x | Q1 | A | x | x | Q1 | A | x | Q1 | A | |||||||||||||||||
27 | e | end of month | date | 2021-Q1 | B | y | 7 | B | y | 03-Mar | B | y | Q1 | B | y | y | Q1 | B | y | Q1 | B | |||||||||||||||||
28 | m | month | text | yyyy-mm-mmm | 2019-Q4 | A | y | 19 | A | y | 12-Dec | A | y | Q4 | A | y | y | Q4 | A | y | Q4 | A | ||||||||||||||||
29 | y | year | numeric | 2020-Q3 | B | y | 5 | B | y | 08-Aug | B | y | Q3 | B | y | y | Q3 | B | y | Q1 | A | |||||||||||||||||
30 | q | quarter | text | yyyy-Qn | 2019-Q2 | B | x | 8 | B | x | 05-May | B | x | Q2 | B | x | x | Q2 | B | y | Q1 | A | ||||||||||||||||
31 | od | only date | numeric | 2020-Q1 | A | y | 6 | A | y | 02-Feb | A | y | Q1 | A | y | y | Q1 | A | x | Q1 | B | |||||||||||||||||
32 | om | only month | text | dd-mmm | 2020-Q1 | A | y | 9 | A | y | 02-Feb | A | y | Q1 | A | y | y | Q1 | A | x | Q4 | A | ||||||||||||||||
33 | oq | only quarter | text | Qn | 2019-Q1 | B | x | 9 | B | x | 01-Jan | B | x | Q1 | B | x | x | Q1 | B | y | Q1 | A | ||||||||||||||||
34 | 2020-Q4 | A | x | 24 | A | x | 11-Nov | A | x | Q4 | A | x | x | Q4 | A | y | Q1 | A | ||||||||||||||||||||
35 | Note:ADVFLT criteria arguments should follow | 2020-Q2 | B | y | 28 | B | y | 04-Apr | B | y | Q2 | B | y | y | Q2 | B | x | Q1 | B | |||||||||||||||||||
36 | the t (type) argument format of ADATE(see table) | 2021-Q2 | A | x | 1 | A | x | 04-Apr | A | x | Q2 | A | x | x | Q2 | A | y | Q1 | B | |||||||||||||||||||
37 | 2021-Q2 | B | x | 16 | B | x | 04-Apr | B | x | Q2 | B | x | x | Q2 | B | y | Q4 | A | ||||||||||||||||||||
38 | other functions on minisheet | 2020-Q1 | A | y | 29 | A | y | 03-Mar | A | y | Q1 | A | y | y | Q1 | A | ||||||||||||||||||||||
39 | ADVFLT | 2021-Q1 | A | y | 16 | A | y | 02-Feb | A | y | Q1 | A | y | y | Q1 | A | ||||||||||||||||||||||
40 | ACOMBINE | 2021-Q1 | B | x | 21 | B | x | 03-Mar | B | x | Q1 | B | x | x | Q1 | B | list all possible combinations | |||||||||||||||||||||
41 | ARRANGE | 2019-Q2 | A | x | 18 | A | x | 06-Jun | A | x | Q2 | A | x | x | Q2 | A | =ACOMBINE(AA26#,{2,3,1}) | |||||||||||||||||||||
42 | 2020-Q2 | A | x | 23 | A | x | 04-Apr | A | x | Q2 | A | x | x | Q2 | A | Q1 | A | x | ||||||||||||||||||||
43 | 2019-Q1 | B | y | 1 | B | y | 01-Jan | B | y | Q1 | B | y | y | Q1 | B | Q1 | A | y | ||||||||||||||||||||
44 | 2020-Q4 | A | y | 8 | A | y | 10-Oct | A | y | Q4 | A | y | y | Q4 | A | Q1 | B | x | ||||||||||||||||||||
45 | Q1 | B | y | |||||||||||||||||||||||||||||||||||
46 | Q4 | A | x | |||||||||||||||||||||||||||||||||||
47 | Q4 | A | y | |||||||||||||||||||||||||||||||||||
48 | Q4 | B | x | |||||||||||||||||||||||||||||||||||
49 | Q4 | B | y | |||||||||||||||||||||||||||||||||||
50 | ||||||||||||||||||||||||||||||||||||||
ADATE post |
Cell Formulas | ||
---|---|---|
Range | Formula | |
A1 | A1 | =FORMULATEXT(A3) |
G2,K2,O2,AA41,AA25,G24,K24,O24,S24,W24,AF13,S2,W2,AA2,AF2 | G2 | =FORMULATEXT(G3) |
A3:A22 | A3 | =RANDARRAY(20,,"1-01-19",TODAY(),1) |
G3:I22 | G3 | =ADATE(C3:E22,"f",) |
K3:M22 | K3 | =ADATE(C3:E22,"e",) |
O3:Q22 | O3 | =ADATE(C3:E22,"m",) |
S3:U22 | S3 | =ADATE(C3:E22,"y",) |
W3:Y22 | W3 | =ARRANGE(C3:E22,{3,1}) |
AA3:AC22 | AA3 | =ADATE(W3#,"m",2) |
AF3:AH9 | AF3 | =ADVFLT(AA3#,{2,2},{">=2019-03-mar","<=2020-03-mar"},,) |
AF14:AH25 | AF14 | =ADVFLT(AA3#,,,{2,2},{"<=2019-12-dec",">2020-12-dec"}) |
G25:I44 | G25 | =ADATE(C3:E22,"q",) |
K25:M44 | K25 | =ADATE(C3:E22,"od",) |
O25:Q44 | O25 | =ADATE(C3:E22,"om",) |
S25:U44 | S25 | =ADATE(C3:E22,"oq",) |
W25:Y44 | W25 | =ADATE(W3#,"oq",2) |
AA26:AC37 | AA26 | =ADVFLT(W25#,,,{2,2},{"q1","q4"}) |
AA42:AC49 | AA42 | =ACOMBINE(AA26#,{2,3,1}) |
Dynamic array formulas. |
Upvote
0