ADATE

Xlambda · May 12, 2021

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

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

=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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

44112

A

y

2020-10-Oct

A

y

2021-02-Feb

A

23

x

2021-03-Mar

B

24

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

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

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

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

Q4

A

y

Q4

A

29

y

year

numeric

2020-Q3

B

y

5

B

y

08-Aug

B

y

Q3

B

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

Q2

B

y

Q1

A

31

od

only date

numeric

2020-Q1

A

y

6

A

y

02-Feb

A

y

Q1

A

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

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

Q1

B

y

Q1

A

34

2020-Q4

A

x

24

A

x

11-Nov

A

x

Q4

A

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

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

Q2

A

y

Q1

B

37

2021-Q2

B

x

16

B

x

04-Apr

B

x

Q2

B

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

Q1

A

39

ADVFLT

2021-Q1

A

y

16

A

y

02-Feb

A

y

Q1

A

y

Q1

A

40

ACOMBINE

2021-Q1

B

x

21

B

x

03-Mar

B

x

Q1

B

x

Q1

B

list all possible combinations

41

ARRANGE

2019-Q2

A

x

18

A

x

06-Jun

A

x

Q2

A

x

Q2

A

=ACOMBINE(AA26#,{2,3,1})

42

2020-Q2

A

x

23

A

x

04-Apr

A

x

Q2

A

x

Q2

A

Q1

A

x

43

2019-Q1

B

y

1

B

y

01-Jan

B

y

Q1

B

y

Q1

B

Q1

A

y

44

2020-Q4

A

y

8

A

y

10-Oct

A

y

Q4

A

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.

Xlambda · Sep 11, 2022

TEXT.NDT does to dates serial numbers, positive or negative, whatever TEXT(sn,"date format") does to regular positive dates serial numbers.
TEXT.NDT(sn,[ft]) Text Format Negative Dates function.
sn: serial numbers array
[ft]: format argument, any of the accepted text patterns for date formats.
- if omitted, pattern => "dd-mm-yyy"
When comes to negative sn, makes sense to use for years, 4 digits representation (at least 3 "y"'s)

Excel Formula:

=LAMBDA(sn, [ft],
    LET(
        f, IF(ft = "", "dd-mm-yyy", ft),
        i, sn > 0,
        s, IF(i, sn, sn + 146098),
        y, YEAR(s),
        t, TEXT(s, f),
        IF(i, t, SUBSTITUTE(t, y, y - 400))
    )
)

Neg sn dates.xlsx

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

1

2

=RANDARRAY(40,,1600,2022,1)

=TEXT.NDT(F6#,"dd-mmm-yyy")

3

↓↓↓

=RANDARRAY(40,,1,12,1)

↓↓↓

=TEXT.NDT(F6#,"ddd")

4

↓↓↓

=RANDARRAY(40,,1,31,1)

↓↓↓

=--TEXT.NDT(F6#,"d")

5

↓↓↓

↓↓↓

6

7

1684

2

15

-78846

15-02-1684

15-Feb-1684

Tue

15

2

8

1810

7

8

-32683

08-07-1810

08-Jul-1810

Sun

8

7

9

1724

12

1

-63947

01-12-1724

01-Dec-1724

Fri

1

12

10

1837

10

25

-22712

25-10-1837

25-Oct-1837

Wed

25

10

11

1617

1

26

-103337

26-01-1617

26-Jan-1617

Thu

26

1

12

1800

6

31

-36342

01-07-1800

01-Jul-1800

Tue

1

7

13

1606

3

4

-107318

04-03-1606

04-Mar-1606

Sat

4

3

14

1895

6

18

-1657

18-06-1895

18-Jun-1895

Tue

18

6

15

1767

6

27

-48399

27-06-1767

27-Jun-1767

Sat

27

6

16

1626

11

-99761

11-11-1626

11-Nov-1626

Wed

11

17

1928

1

4

10231

04-01-1928

04-Jan-1928

Wed

4

1

18

1989

4

27

32625

27-04-1989

27-Apr-1989

Thu

27

4

19

1953

5

27

19506

27-05-1953

27-May-1953

Wed

27

5

20

1934

11

17

12740

17-11-1934

17-Nov-1934

Sat

17

11

21

1730

2

5

-62055

05-02-1730

05-Feb-1730

Sun

5

2

22

1734

7

8

-60441

08-07-1734

08-Jul-1734

Thu

8

7

23

1706

1

-70856

01-01-1706

01-Jan-1706

Fri

1

24

2015

8

10

42226

10-08-2015

10-Aug-2015

Mon

10

8

25

1861

4

5

-14149

05-04-1861

05-Apr-1861

Fri

5

4

26

1993

9

21

34233

21-09-1993

21-Sep-1993

Tue

21

9

27

1960

2

25

21971

25-02-1960

25-Feb-1960

Thu

25

2

28

1705

6

30

-71041

30-06-1705

30-Jun-1705

Tue

30

6

29

1813

8

2

-31562

02-08-1813

02-Aug-1813

Mon

2

8

30

1893

6

24

-2381

24-06-1893

24-Jun-1893

Sat

24

6

31

1967

10

24

24769

24-10-1967

24-Oct-1967

Tue

24

10

32

1819

12

5

-29246

05-12-1819

05-Dec-1819

Sun

5

12

33

1732

11

4

-61052

04-11-1732

04-Nov-1732

Tue

4

11

34

1911

7

22

4221

22-07-1911

22-Jul-1911

Sat

22

7

35

1795

10

1

-38076

01-10-1795

01-Oct-1795

Thu

1

10

36

2005

2

11

38394

11-02-2005

11-Feb-2005

Fri

11

2

37

1683

8

31

-79014

31-08-1683

31-Aug-1683

Tue

31

8

38

1694

10

6

-74960

06-10-1694

06-Oct-1694

Wed

6

10

39

1829

5

6

-25806

06-05-1829

06-May-1829

Wed

6

5

40

1669

2

16

-84323

16-02-1669

16-Feb-1669

Sat

16

2

41

1936

8

15

13377

15-08-1936

15-Aug-1936

Sat

15

8

42

1822

11

1

-28184

01-11-1822

01-Nov-1822

Fri

1

11

43

1750

7

29

-54576

29-07-1750

29-Jul-1750

Wed

29

7

44

1813

12

2

-31440

02-12-1813

02-Dec-1813

Thu

2

12

45

1651

5

15

-90810

15-05-1651

15-May-1651

Mon

15

5

46

TEXT.NDT

Cell Formulas
Range	Formula
B2,J2	B2	=FORMULATEXT(B6)
C3,L3	C3	=FORMULATEXT(C6)
D4,N4	D4	=FORMULATEXT(D6)
F5,H5,P5	F5	=FORMULATEXT(F6)
B6:B45	B6	=RANDARRAY(40,,1600,2022,1)
C6:C45	C6	=RANDARRAY(40,,1,12,1)
D6:D45	D6	=RANDARRAY(40,,1,31,1)
F6:F45	F6	=NDT(B6#,C6#,D6#)
H6:H45	H6	=TEXT.NDT(F6#)
J6:J45	J6	=TEXT.NDT(F6#,"dd-mmm-yyy")
L6:L45	L6	=TEXT.NDT(F6#,"ddd")
N6:N45	N6	=--TEXT.NDT(F6#,"d")
P6:P45	P6	=--TEXT.NDT(F6#,"m")
Dynamic array formulas.

Xlambda · Sep 11, 2022

Neg sn dates.xlsx

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

1

Extracting leap years using 29 Feb method if text.ndt(ndt(y,2,29),"m")=2 => leap year

2

3

=FILTER(B6#,F6#=2)

checking 400y leap cycle

4

=RANDARRAY(40,,1600,2500,1)

if m=2 => leap year

↓↓↓

=FILTER(B6#,F6#=3)

5

↓↓↓

1600

6

1684

2494

1700

no

7

2494

217016

3

1912

1969

1800

no

8

1969

25263

3

2024

2218

2000

leap

9

2218

116208

3

1896

2003

2100

no

10

2003

37681

3

2400

2110

2200

no

11

1860

-14550

2

1716

2186

2300

no

12

2110

76762

3

2244

1990

2400

leap

13

2186

104521

3

2340

2270

14

1990

32933

3

2020

2061

O5:

=IF(TEXT.NDT(NDT(M5:M12,2,29),"m")="2","leap","no")

15

2270

135201

3

1625

16

1684

-78832

2

2239

17

2061

58866

3

2133

18

1912

4443

2

1641

19

1625

-100381

3

2225

20

2239

123878

3

1745

21

2133

85163

3

2323

22

1641

-94537

3

2130

23

2225

118765

3

1971

24

1745

-56552

3

1738

25

2323

154558

3

1635

26

2130

84067

3

1802

27

1971

25993

3

1611

28

1738

-59109

3

1997

29

1635

-96729

3

1786

30

2024

45351

2

1635

31

1896

-1401

2

2402

32

1802

-35734

3

1830

33

2400

182682

2

2303

34

1716

-67145

2

2221

35

1611

-105495

3

36

1997

35490

3

37

1786

-41577

3

38

2244

125704

2

39

1635

-96729

3

40

2340

160767

2

41

2402

183413

3

42

1830

-25507

3

43

2303

147253

3

44

2020

43890

2

45

2221

117304

3

46

TEXT.NDT 1

Cell Formulas
Range	Formula
H3,B4	H3	=FORMULATEXT(H5)
J4,D5,F5	J4	=FORMULATEXT(J5)
H5:H14	H5	=FILTER(B6#,F6#=2)
J5:J34	J5	=FILTER(B6#,F6#=3)
O5:O12	O5	=IF(TEXT.NDT(NDT(M5:M12,2,29),"m")="2","leap","no")
B6:B45	B6	=RANDARRAY(40,,1600,2500,1)
D6:D45	D6	=NDT(B6#,2,29)
F6:F45	F6	=--TEXT.NDT(D6#,"m")
M14	M14	=FORMULATEXT(O5)
Dynamic array formulas.

Xlambda · Sep 11, 2022

NDT.TEXT returns sn of an array of dates properly formatted as text. Does what --(text) does to positive dates formatted as text.
NDT.TEXT(tx)
tx: text array

Excel Formula:

=LAMBDA(tx,
    LET(
        r, REDUCE(tx, SEQUENCE(300, , 1600), LAMBDA(v, i, SUBSTITUTE(v, i, i + 400))),
        IFERROR(--tx, --r - 146098)
    )
)

Neg sn dates.xlsx

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

1

NDT.TEXT(tx)

2

3

random sn's

4

=RANDARRAY(3,15,-109572,TODAY(),1)

5

-13778

-10836

-11319

-102627

41452

3825

-3355

-4525

-75218

-4397

-36706

-56334

-63266

14999

-64159

6

-54507

14391

-46339

-32841

-74104

41791

1273

-107424

12770

-23083

-108023

-27007

3281

-52373

-39085

7

1

36466

-3635

-17884

-31825

-99509

-48002

-44647

6865

-60899

-102063

-58117

-29379

-37664

2364

9390

8

9

=TEXT.NDT(B5#)

10

11-04-1862

01-05-1870

03-01-1869

06-01-1619

27-06-2013

21-06-1910

24-10-1890

11-08-1887

21-01-1694

17-12-1887

02-07-1799

05-10-1745

13-10-1726

23-01-1941

03-05-1724

11

06-10-1750

26-05-1939

15-02-1773

31-01-1810

08-02-1697

01-06-2014

26-06-1903

18-11-1605

17-12-1934

19-10-1836

29-03-1604

21-01-1826

24-12-1908

09-08-1756

26-12-1792

12

02-11-1999

17-01-1890

13-01-1851

12-11-1812

21-07-1627

28-07-1768

04-10-1777

17-10-1918

06-04-1733

23-07-1620

17-11-1740

25-07-1819

16-11-1796

21-06-1906

15-09-1925

13

14

=TEXT.NDT(B5#,"d-mmm-yyy")

15

11-Apr-1862

1-May-1870

3-Jan-1869

6-Jan-1619

27-Jun-2013

21-Jun-1910

24-Oct-1890

11-Aug-1887

21-Jan-1694

17-Dec-1887

2-Jul-1799

5-Oct-1745

13-Oct-1726

23-Jan-1941

3-May-1724

16

6-Oct-1750

26-May-1939

15-Feb-1773

31-Jan-1810

8-Feb-1697

1-Jun-2014

26-Jun-1903

18-Nov-1605

17-Dec-1934

19-Oct-1836

29-Mar-1604

21-Jan-1826

24-Dec-1908

9-Aug-1756

26-Dec-1792

17

2-Nov-1999

17-Jan-1890

13-Jan-1851

12-Nov-1812

21-Jul-1627

28-Jul-1768

4-Oct-1777

17-Oct-1918

6-Apr-1733

23-Jul-1620

17-Nov-1740

25-Jul-1819

16-Nov-1796

21-Jun-1906

15-Sep-1925

18

19

=NDT.TEXT(B10#)

20

-13778

-10836

-11319

-102627

41452

3825

-3355

-4525

-75218

-4397

-36706

-56334

-63266

14999

-64159

21

-54507

14391

-46339

-32841

-74104

41791

1273

-107424

12770

-23083

-108023

-27007

3281

-52373

-39085

22

2

36466

-3635

-17884

-31825

-99509

-48002

-44647

6865

-60899

-102063

-58117

-29379

-37664

2364

9390

23

24

=NDT.TEXT(B15#)

25

-13778

-10836

-11319

-102627

41452

3825

-3355

-4525

-75218

-4397

-36706

-56334

-63266

14999

-64159

26

-54507

14391

-46339

-32841

-74104

41791

1273

-107424

12770

-23083

-108023

-27007

3281

-52373

-39085

27

3

36466

-3635

-17884

-31825

-99509

-48002

-44647

6865

-60899

-102063

-58117

-29379

-37664

2364

9390

28

29

check if all 3 sn's arrays are identical

30

=SUM(B20#-B5#+B25#-B5#)=0

31

TRUE

32

NDT.TEXT

Cell Formulas
Range	Formula
B4,B30,B24,B19,B14,B9	B4	=FORMULATEXT(B5)
B5:P7	B5	=RANDARRAY(3,15,-109572,TODAY(),1)
B10:P12	B10	=TEXT.NDT(B5#)
B15:P17	B15	=TEXT.NDT(B5#,"d-mmm-yyy")
B20:P22,B25:P27	B20	=NDT.TEXT(B10#)
B31	B31	=SUM(B20#-B5#+B25#-B5#)=0
Dynamic array formulas.

Xlambda · Sep 11, 2022

Neg sn dates.xlsx

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

1

FOM and EOM for an array of sn (negative or positive)

2

random sn's

3

=RANDARRAY(3,15,-109572,TODAY(),1)

4

-47349

-75662

-19902

-59596

-78183

-83444

-77401

43074

-23583

-101957

-92138

-106755

-81287

-50272

-78805

5

-79060

-23893

-33216

139

-80032

-78399

41448

-85290

-12038

-76180

-52550

-92619

-84771

36586

26467

6

-103448

7768

-52155

-66979

-41146

17784

-58278

-62823

-10414

11988

-29270

-43680

33448

-81224

-97307

7

8

full date of sn

9

=TEXT.NDT(B4#,"d-m-yyy")

10

12-5-1770

3-11-1692

5-7-1845

30-10-1736

9-12-1685

15-7-1671

30-1-1688

5-12-2017

7-6-1835

6-11-1620

25-9-1647

18-9-1607

10-6-1677

11-5-1762

27-3-1684

11

16-7-1683

1-8-1834

21-1-1809

18-5-1900

16-11-1680

7-5-1685

23-6-2013

25-6-1666

15-1-1867

4-6-1691

14-2-1756

1-6-1646

26-11-1667

1-3-2000

17-6-1972

12

7-10-1616

7-4-1921

15-3-1757

13-8-1716

6-5-1787

8-9-1948

9-6-1740

30-12-1727

27-6-1871

26-10-1932

11-11-1819

28-5-1780

29-7-1991

12-8-1677

31-7-1633

13

14

sn's 1st of month (FOM)

15

=NDT.TEXT(TEXT.NDT(B4#,"m-yyy"))

16

-47360

-75664

-19906

-59625

-78191

-83458

-77430

43070

-23589

-101962

-92162

-106772

-81296

-50282

-78831

17

-79075

-23893

-33236

122

-80047

-78405

41426

-85314

-12052

-76183

-52563

-92619

-84796

36586

26451

18

-103454

7762

-52169

-66991

-41151

17777

-58286

-62852

-10440

11963

-29280

-43707

33420

-81235

-97337

19

20

check FOM

21

=TEXT.NDT(B16#)

22

01-05-1770

01-11-1692

01-07-1845

01-10-1736

01-12-1685

01-07-1671

01-01-1688

01-12-2017

01-06-1835

01-11-1620

01-09-1647

01-09-1607

01-06-1677

01-05-1762

01-03-1684

23

01-07-1683

01-08-1834

01-01-1809

01-05-1900

01-11-1680

01-05-1685

01-06-2013

01-06-1666

01-01-1867

01-06-1691

01-02-1756

01-06-1646

01-11-1667

01-03-2000

01-06-1972

24

01-10-1616

01-04-1921

01-03-1757

01-08-1716

01-05-1787

01-09-1948

01-06-1740

01-12-1727

01-06-1871

01-10-1932

01-11-1819

01-05-1780

01-07-1991

01-08-1677

01-07-1633

25

26

sn's end of month(EOM) (a separate function can be designed if needed)

27

=NDT(--TEXT.NDT(B4#,"yyy"),TEXT.NDT(B4#,"m")+1,1)-1

28

-47330

-75635

-19876

-59595

-78161

-83428

-77400

43100

-23560

-101933

-92133

-106743

-81267

-50252

-78801

29

-79045

-23863

-33206

152

-80018

-78375

41455

-85285

-12022

-76154

-52535

-92590

-84767

36616

26480

30

-103424

7791

-52139

-66961

-41121

17806

-58257

-62822

-10411

11993

-29251

-43677

33450

-81205

-97307

31

32

check EOM

33

=TEXT.NDT(B28#)

34

31-05-1770

30-11-1692

31-07-1845

31-10-1736

31-12-1685

31-07-1671

31-01-1688

31-12-2017

30-06-1835

30-11-1620

30-09-1647

30-09-1607

30-06-1677

31-05-1762

31-03-1684

35

31-07-1683

31-08-1834

31-01-1809

31-05-1900

30-11-1680

31-05-1685

30-06-2013

30-06-1666

31-01-1867

30-06-1691

29-02-1756

30-06-1646

30-11-1667

31-03-2000

30-06-1972

36

31-10-1616

30-04-1921

31-03-1757

31-08-1716

31-05-1787

30-09-1948

30-06-1740

31-12-1727

30-06-1871

31-10-1932

30-11-1819

31-05-1780

31-07-1991

31-08-1677

31-07-1633

37

NDT.TEXT 1

Cell Formulas
Range	Formula
B3,B33,B27,B21,B15,B9	B3	=FORMULATEXT(B4)
B4:P6	B4	=RANDARRAY(3,15,-109572,TODAY(),1)
B10:P12	B10	=TEXT.NDT(B4#,"d-m-yyy")
B16:P18	B16	=NDT.TEXT(TEXT.NDT(B4#,"m-yyy"))
B22:P24,B34:P36	B22	=TEXT.NDT(B16#)
B28:P30	B28	=NDT(--TEXT.NDT(B4#,"yyy"),TEXT.NDT(B4#,"m")+1,1)-1
Dynamic array formulas.

Xlambda · Sep 12, 2022

Neg sn dates.xlsx

A

B

C

D

E

F

G

H

I

J

K

L

M

N

1

Dates array sorting

2

=SORTBY(B4:B43,NDT.TEXT(B4:B43))

3

↓↓↓

=TEXT.NDT(SORT(NDT.TEXT(B4:B43)),"dd-mmm-yyy")

4

15-May-1642

02-Mar-1622

5

14-Nov-1693

04-Dec-1635

days between d1 d2

6

19-Apr-1823

26-Sep-1637

d1

01-08-22

<-- numeric value under date format façade

7

20-Jun-1873

15-May-1642

d2

31-08-1777

<-- dates before 1900 always imputed with 4 digits year

8

22-Nov-1881

22-Dec-1650

9

20-Mar-1738

11-Feb-1653

=I6-NDT.TEXT(I7)

<-- when 29feb1900 inside dates interval

10

12-Aug-1698

30-Jan-1658

89455

we do not need the final +1

11

05-Apr-1856

16-Dec-1676

12

02-Nov-1880

10-Jan-1683

d1

31-Aug-1777

13

23-Sep-1978

26-Jan-1686

d2

15-7-1650

14

16-Dec-1676

14-Nov-1693

15

04-Mar-1921

09-Feb-1695

=NDT.TEXT(I12)-NDT.TEXT(I13)+1

16

10-Jan-1683

12-Aug-1698

46434

17

04-Dec-1635

20-Mar-1738

18

26-Sep-1637

02-Jun-1782

sn of today's date 350y ago

19

26-Jan-1686

08-Dec-1814

=LET(d,TODAY(),NDT(YEAR(d)-350,MONTH(d),DAY(d)))

20

19-Jul-1888

19-Apr-1823

-83019

21

08-Dec-1814

07-Sep-1846

22

10-Apr-1934

03-Jan-1853

check

23

03-May-2001

05-Apr-1856

=TEXT.NDT(I20)

24

22-Dec-1650

27-Jul-1868

12-09-1672

25

09-Feb-1695

24-Jan-1871

26

16-Feb-1914

20-Jun-1873

negative dates formulas can deal also with time

27

26-Apr-1878

17-8-1634 13:45

28

30-Jan-1658

02-Nov-1880

29

16-Apr-1979

14-Nov-1880

=NDT.TEXT(I27)

30

14-Nov-1880

22-Nov-1881

-96924.42708

31

20-Aug-1906

19-Jul-1888

32

11-Jun-1915

01-Jul-1906

check

33

01-Jul-1906

20-Aug-1906

=TEXT.NDT(I30,"d-mmm-yyy hh:mm")

34

02-Mar-1622

15-Jan-1910

17-Aug-1634 13:45

35

15-Jan-1910

16-Feb-1914

36

24-Jan-1871

11-Jun-1915

37

11-Feb-1653

04-Mar-1921

38

13-Jan-1929

39

03-Apr-1935

10-Apr-1934

40

02-Jun-1782

03-Apr-1935

41

07-Sep-1846

23-Sep-1978

42

27-Jul-1868

16-Apr-1979

43

03-Jan-1853

03-May-2001

44

NDT.TEXT 2

Cell Formulas
Range	Formula
D2	D2	=FORMULATEXT(D4)
F3,I33,I29,I23,I19,I15,I9	F3	=FORMULATEXT(F4)
D4:D43	D4	=SORTBY(B4:B43,NDT.TEXT(B4:B43))
F4:F43	F4	=TEXT.NDT(SORT(NDT.TEXT(B4:B43)),"dd-mmm-yyy")
I10	I10	=I6-NDT.TEXT(I7)
I16	I16	=NDT.TEXT(I12)-NDT.TEXT(I13)+1
I20	I20	=LET(d,TODAY(),NDT(YEAR(d)-350,MONTH(d),DAY(d)))
I24	I24	=TEXT.NDT(I20)
I30	I30	=NDT.TEXT(I27)
I34	I34	=TEXT.NDT(I30,"d-mmm-yyy hh:mm")
Dynamic array formulas.

ADATE

Xlambda

Well-known Member

Xlambda

Well-known Member

Xlambda

Well-known Member

Xlambda

Well-known Member

Xlambda

Well-known Member

Xlambda

Well-known Member

Forum statistics

Share this page

ADATE

Xlambda

Well-known Member

Xlambda

Well-known Member

Xlambda

Well-known Member

Xlambda

Well-known Member

Xlambda

Well-known Member

Xlambda

Well-known Member

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock