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.

Xlambda · Aug 9, 2024

Mike's latest YT

Remembered that I have covered that here ,2 years ago.
concept: this very thread few posts above: post #8 ADATE
lambdas: TEXT.NDT post#11

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))
    )
)

and NDT.TEXT post#13

Excel Formula:

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

These formulas create a consistent negative Excel date system.
To add something new, here is a lambda to check leap years between to year values
LEAP(ya,yb) calls NDT.TEXT TEXT.NDT
ya,yb any years >= 1600 (any order) (before Gregorian calendar the calendars were incorrect)

Excel Formula:

=LAMBDA(ya, yb,
    LET(
        y, SEQUENCE(ABS(yb - ya) + 1, , MIN(ya, yb)),
        d, NDT.TEXT("29-02-" & y),
        i, ISNUMBER(d),
        f, FILTER(d, i),
        HSTACK(FILTER(y, i), f, TEXT.NDT(f, "ddd dd-mmm-yyy"))
    )
)

NOTE: For US dates, we have only to use correspondent date format and everything will work fine (all years should have 4 digits)
leep years check link List of leap years and leap days since 1600

EMT 1864.xlsx

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

1

Excel negative dates system (must be consistent with Excel date system, continues with negative numbers in order, and retains the 29 Feb 1900 anomaly, everything else acurate after 1-jan-1600)

2

3

=NDT.TEXT(B5:B11)

4

text values

↓↓↓↓

=TEXT.NDT(C5#,"ddd mmm-dd-yyyy")

=LEAP(1600,1720)

=LEAP(1810,1720)

=LEAP(1880,1920)

5

02-01-1900

2

Mon Jan-02-1900

*1

1600

-109513

Tue 29-Feb-1600

1720

-65684

Thu 29-Feb-1720

1880

-7245

Sun 29-Feb-1880

6

01-01-1900

1

Sun Jan-01-1900

1604

-108052

Sun 29-Feb-1604

1724

-64223

Tue 29-Feb-1724

1884

-5784

Fri 29-Feb-1884

7

31-12-1899

0

Sun Dec-31-1899

1608

-106591

Fri 29-Feb-1608

1728

-62762

Sun 29-Feb-1728

1888

-4323

Wed 29-Feb-1888

8

30-12-1899

-1

Sat Dec-30-1899

1612

-105130

Wed 29-Feb-1612

1732

-61301

Fri 29-Feb-1732

1892

-2862

Mon 29-Feb-1892

9

01-01-1800

-36523

Wed Jan-01-1800

1616

-103669

Mon 29-Feb-1616

1736

-59840

Wed 29-Feb-1736

1896

-1401

Sat 29-Feb-1896

10

01-01-1700

-73047

Fri Jan-01-1700

1620

-102208

Sat 29-Feb-1620

1740

-58379

Mon 29-Feb-1740

*

1900

60

Wed 29-Feb-1900

excel anomaly

11

01-01-1600

-109572

Sat Jan-01-1600

1624

-100747

Thu 29-Feb-1624

1744

-56918

Sat 29-Feb-1744

1904

1521

Mon 29-Feb-1904

12

1628

-99286

Tue 29-Feb-1628

1748

-55457

Thu 29-Feb-1748

1908

2982

Sat 29-Feb-1908

13

PQ to Excel and vice versa for sn values <=60

1632

-97825

Sun 29-Feb-1632

1752

-53996

Tue 29-Feb-1752

1912

4443

Thu 29-Feb-1912

14

1636

-96364

Fri 29-Feb-1636

1756

-52535

Sun 29-Feb-1756

1916

5904

Tue 29-Feb-1916

15

PQ sn

PQ date

1640

-94903

Wed 29-Feb-1640

1760

-51074

Fri 29-Feb-1760

1920

7365

Sun 29-Feb-1920

16

-102589

12-02-1619

1644

-93442

Mon 29-Feb-1644

1764

-49613

Wed 29-Feb-1764

17

1648

-91981

Sat 29-Feb-1648

1768

-48152

Mon 29-Feb-1768

=LEAP(2017,1990)

18

To find the date if we have the PQ sn we have to subtract 1

1652

-90520

Thu 29-Feb-1652

1772

-46691

Sat 29-Feb-1772

1992

33663

Sat 29-Feb-1992

19

1656

-89059

Tue 29-Feb-1656

1776

-45230

Thu 29-Feb-1776

1996

35124

Thu 29-Feb-1996

20

=TEXT.NDT(B16-1)

1660

-87598

Sun 29-Feb-1660

1780

-43769

Tue 29-Feb-1780

*

2000

36585

Tue 29-Feb-2000

21

PQ sn to date

12-02-1619

1664

-86137

Fri 29-Feb-1664

1784

-42308

Sun 29-Feb-1784

2004

38046

Sun 29-Feb-2004

22

1668

-84676

Wed 29-Feb-1668

1788

-40847

Fri 29-Feb-1788

2008

39507

Fri 29-Feb-2008

23

To find the PQ sn if we have date we have to add 1

1672

-83215

Mon 29-Feb-1672

1792

-39386

Wed 29-Feb-1792

2012

40968

Wed 29-Feb-2012

24

1676

-81754

Sat 29-Feb-1676

*

1796

-37925

Mon 29-Feb-1796

2016

42429

Mon 29-Feb-2016

25

=NDT.TEXT(C16)+1

1680

-80293

Thu 29-Feb-1680

1804

-35004

Wed 29-Feb-1804

26

-102589

date to PQ sn

1684

-78832

Tue 29-Feb-1684

1808

-33543

Mon 29-Feb-1808

*1900 should not be leap year but in Excel it is

27

1688

-77371

Sun 29-Feb-1688

*2000 is a leap year

28

1692

-75910

Fri 29-Feb-1692

*1800 not leap year

29

*2

1696

-74449

Wed 29-Feb-1696

30

1704

-71528

Fri 29-Feb-1704

31

1708

-70067

Wed 29-Feb-1708

32

1712

-68606

Mon 29-Feb-1712

33

1716

-67145

Sat 29-Feb-1716

34

1720

-65684

Thu 29-Feb-1720

35

36

*1 1600 is leap year

37

*2 1700 not leap year

38

Sheet1

Cell Formulas
Range	Formula
C3	C3	=FORMULATEXT(C5)
D4,B25,C20,O17,O4,K4,G4	D4	=FORMULATEXT(D5)
C5:C11	C5	=NDT.TEXT(B5:B11)
D5:D11	D5	=TEXT.NDT(C5#,"ddd mmm-dd-yyyy")
G5:I34	G5	=LEAP(1600,1720)
K5:M26	K5	=LEAP(1810,1720)
O5:Q15	O5	=LEAP(1880,1920)
O18:Q24	O18	=LEAP(2017,1990)
C21	C21	=TEXT.NDT(B16-1)
B26	B26	=NDT.TEXT(C16)+1
Dynamic array formulas.

mgirvin · Aug 11, 2024

Wow!!!! Historians are now so happy to have your LAMBDAs NDT, NDT.TEXT and TEXT.NDT to create a consistent negative Excel date system. ExcelLambda, you much better and smarter than the programmers at MS lol

Thanks for bringing the Team such LAMBDA power : )

mgirvin · Aug 11, 2024

I tested it and got these results:

Text Date	12-25-1700
NDT =NDT(1700,12,25)	-72689
NDT .TEXT =NDT.TEXT("12-25-1700")	-72689
TEXT.NDT =TEXT.NDT(C60,"mmm-dd-yyy")	Dec-25-1700

Now I am like: "Wow^100" : ) : )

Xlambda · Aug 12, 2024

Thank you, Mike, for your kind words and for testing that the negative dates functions are also consistent with US dates formats.
More consistency tests and fun fact about DATE:

EMT 1864.xlsx

A

B

C

D

E

F

G

H

I

1

2

Another proof that NDT is as consistent as DATE function is, same behavior

3

4

Date behavior when explicit date values do not exist like day 32 month 13

5

=DATE(2024,13,32)

=TEXT(B6,"ddd dd-mmm-yyy")

6

45689

Sat 01-Feb-2025

7

8

Same inexisting values but 400 years before.

9

=NDT(1624,13,32)

=TEXT.NDT(B10,"ddd dd-mmm-yyy")

10

-100409

Sat 01-Feb-1625

11

12

As we see , same day, same month, same wkdy, only 400 years before

13

If consistent, days gap coresponding to 400 years should be 146098

14

=B6-B10

15

146098

16

17

NDT and DATE "catch up" alike when dates values are "offset"

18

19

Also same behavior when date text format is not an existing date. (2021 1621 not leap years)

20

=--(B21)

21

29-Feb-2021

#VALUE!

22

23

=NDT.TEXT(B24)

24

29-Feb-1621

#VALUE!

25

26

28 Feb has no probl.

27

=NDT.TEXT(B28)

=TEXT.NDT(D28,"ddd dd-mm-yyy")

28

28-Feb-1621

-101843

Sun 28-02-1621

29

30

Fun fact: when as year argument of DATE is <1900 DATE adds 1900 to it

31

32

=DATE(1800,1,1)

=TEXT(B33,"dd-mmm-yyy")

33

657439

01-Jan-3700

34

35

=1800+1900

36

3700

37

Sheet2

Cell Formulas
Range	Formula
B5,D5,E35,E32,B32,D27:E27,D23,D20,B14,B9,D9	B5	=FORMULATEXT(B6)
B6	B6	=DATE(2024,13,32)
D6	D6	=TEXT(B6,"ddd dd-mmm-yyy")
B10	B10	=NDT(1624,13,32)
D10	D10	=TEXT.NDT(B10,"ddd dd-mmm-yyy")
B15	B15	=B6-B10
D21	D21	=--(B21)
D24,D28	D24	=NDT.TEXT(B24)
E28	E28	=TEXT.NDT(D28,"ddd dd-mm-yyy")
B33	B33	=DATE(1800,1,1)
E33	E33	=TEXT(B33,"dd-mmm-yyy")
E36	E36	=1800+1900

mgirvin · Aug 12, 2024

The NDT nonexistence and offsetting is cooler than the other side of the pillow, sorta like XlLambda : ) : ) : ) : )

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

Xlambda

Well-known Member

mgirvin

Well-known Member

mgirvin

Well-known Member

Xlambda

Well-known Member

mgirvin

Well-known Member

Forum statistics

Share this page

ADATE

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

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