ADATE

=ADATE(a,t,cl)

a
array
t
type, string, only one of this values "f", "e", "m" , "y" , "q" , "od" , "om" ,"oq"
cl
integer, column index of dates. if ignored or 1, first column will be changed. if more columns have dates format and needs to be replaced we have to use nested ADATE(ADATE(a,"m",),"y",4)

replaces only the dates column values of an array with other data types formats, leaving the rest of the array intact

Xlambda

Well-known Member
Joined
Mar 8, 2021
Messages
860
Office Version
  1. 365
Platform
  1. 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
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
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJ
1=RANDARRAY(20,,"1-01-19",TODAY(),1)sample 2filter btw Mar-2019 and Mar 2020
2rnd dates generatorsample 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"},,)
330-04-2005-05-19Bx01-05-19Bx31-05-19Bx2019-05-MayBx2019Bxx43590Bx2019-05-MayBx2019-05-MayB
401-09-1927-02-21Ax01-02-21Ax28-02-21Ax2021-02-FebAx2021Axx44254Ax2021-02-FebAy2019-12-DecA
512-03-2107-03-21By01-03-21By31-03-21By2021-03-MarBy2021Byy44262By2021-03-MarBx2019-05-MayB
630-04-2119-12-19Ay01-12-19Ay31-12-19Ay2019-12-DecAy2019Ayy43818Ay2019-12-DecAy2020-02-FebA
721-01-2005-08-20By01-08-20By31-08-20By2020-08-AugBy2020Byy44048By2020-08-AugBy2020-02-FebA
820-07-2008-05-19Bx01-05-19Bx31-05-19Bx2019-05-MayBx2019Bxx43593Bx2019-05-MayBy2020-03-MarA
915-09-1906-02-20Ay01-02-20Ay29-02-20Ay2020-02-FebAy2020Ayy43867Ay2020-02-FebAx2019-06-JunA
1021-06-2009-02-20Ay01-02-20Ay29-02-20Ay2020-02-FebAy2020Ayy43870Ay2020-02-FebA
1109-05-1909-01-19Bx01-01-19Bx31-01-19Bx2019-01-JanBx2019Bxx43474Bx2019-01-JanBadvanced filter options are endless
1230-08-1924-11-20Ax01-11-20Ax30-11-20Ax2020-11-NovAx2020Axx44159Ax2020-11-NovAfilter all values except year 2020
1302-09-2028-04-20By01-04-20By30-04-20By2020-04-AprBy2020Byy43949By2020-04-AprB=ADVFLT(AA3#,,,{2,2},{"<=2019-12-dec",">2020-12-dec"})
1402-07-2001-04-21Ax01-04-21Ax30-04-21Ax2021-04-AprAx2021Axx44287Ax2021-04-AprAx2019-05-MayB
1502-02-2016-04-21Bx01-04-21Bx30-04-21Bx2021-04-AprBx2021Bxx44302Bx2021-04-AprBx2021-02-FebA
1621-05-1929-03-20Ay01-03-20Ay31-03-20Ay2020-03-MarAy2020Ayy43919Ay2020-03-MarAy2021-03-MarB
1724-02-1916-02-21Ay01-02-21Ay28-02-21Ay2021-02-FebAy2021Ayy44243Ay2021-02-FebAy2019-12-DecA
1806-01-1921-03-21Bx01-03-21Bx31-03-21Bx2021-03-MarBx2021Bxx44276Bx2021-03-MarBx2019-05-MayB
1930-08-2018-06-19Ax01-06-19Ax30-06-19Ax2019-06-JunAx2019Axx43634Ax2019-06-JunAx2019-01-JanB
2013-02-2023-04-20Ax01-04-20Ax30-04-20Ax2020-04-AprAx2020Axx43944Ax2020-04-AprAx2021-04-AprA
2121-04-2001-01-19By01-01-19By31-01-19By2019-01-JanBy2019Byy43466By2019-01-JanBx2021-04-AprB
2228-07-1908-10-20Ay01-10-20Ay31-10-20Ay2020-10-OctAy2020Ayy44112Ay2020-10-OctAy2021-02-FebA
23x2021-03-MarB
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 timex2019-06-JunA
25t (type) argumentdata typeformat2019-Q2Bx5Bx05-MayBxQ2BxxQ2B=ADVFLT(W25#,,,{2,2},{"q1","q4"})y2019-01-JanB
26ffirst of monthdate2021-Q1Ax27Ax02-FebAxQ1AxxQ1AxQ1A
27eend of monthdate2021-Q1By7By03-MarByQ1ByyQ1ByQ1B
28mmonthtextyyyy-mm-mmm2019-Q4Ay19Ay12-DecAyQ4AyyQ4AyQ4A
29yyearnumeric2020-Q3By5By08-AugByQ3ByyQ3ByQ1A
30qquartertextyyyy-Qn2019-Q2Bx8Bx05-MayBxQ2BxxQ2ByQ1A
31odonly datenumeric2020-Q1Ay6Ay02-FebAyQ1AyyQ1AxQ1B
32omonly monthtextdd-mmm2020-Q1Ay9Ay02-FebAyQ1AyyQ1AxQ4A
33oqonly quartertextQn2019-Q1Bx9Bx01-JanBxQ1BxxQ1ByQ1A
342020-Q4Ax24Ax11-NovAxQ4AxxQ4AyQ1A
35Note:ADVFLT criteria arguments should follow 2020-Q2By28By04-AprByQ2ByyQ2BxQ1B
36the t (type) argument format of ADATE(see table)2021-Q2Ax1Ax04-AprAxQ2AxxQ2AyQ1B
372021-Q2Bx16Bx04-AprBxQ2BxxQ2ByQ4A
38other functions on minisheet2020-Q1Ay29Ay03-MarAyQ1AyyQ1A
39ADVFLT2021-Q1Ay16Ay02-FebAyQ1AyyQ1A
40ACOMBINE2021-Q1Bx21Bx03-MarBxQ1BxxQ1Blist all possible combinations
41ARRANGE2019-Q2Ax18Ax06-JunAxQ2AxxQ2A=ACOMBINE(AA26#,{2,3,1})
422020-Q2Ax23Ax04-AprAxQ2AxxQ2AQ1Ax
432019-Q1By1By01-JanByQ1ByyQ1BQ1Ay
442020-Q4Ay8Ay10-OctAyQ4AyyQ4AQ1Bx
45Q1By
46Q4Ax
47Q4Ay
48Q4Bx
49Q4By
50
ADATE post
Cell Formulas
RangeFormula
A1A1=FORMULATEXT(A3)
G2,K2,O2,AA41,AA25,G24,K24,O24,S24,W24,AF13,S2,W2,AA2,AF2G2=FORMULATEXT(G3)
A3:A22A3=RANDARRAY(20,,"1-01-19",TODAY(),1)
G3:I22G3=ADATE(C3:E22,"f",)
K3:M22K3=ADATE(C3:E22,"e",)
O3:Q22O3=ADATE(C3:E22,"m",)
S3:U22S3=ADATE(C3:E22,"y",)
W3:Y22W3=ARRANGE(C3:E22,{3,1})
AA3:AC22AA3=ADATE(W3#,"m",2)
AF3:AH9AF3=ADVFLT(AA3#,{2,2},{">=2019-03-mar","<=2020-03-mar"},,)
AF14:AH25AF14=ADVFLT(AA3#,,,{2,2},{"<=2019-12-dec",">2020-12-dec"})
G25:I44G25=ADATE(C3:E22,"q",)
K25:M44K25=ADATE(C3:E22,"od",)
O25:Q44O25=ADATE(C3:E22,"om",)
S25:U44S25=ADATE(C3:E22,"oq",)
W25:Y44W25=ADATE(W3#,"oq",2)
AA26:AC37AA26=ADVFLT(W25#,,,{2,2},{"q1","q4"})
AA42:AC49AA42=ACOMBINE(AA26#,{2,3,1})
Dynamic array formulas.
 
Upvote 0
Just added new type argument functionality for weekdays only "ow" , (only weekdays) . An example will follow.
Format is "[wkday index] ddd" , with Monday being first day of the week (like 1 Mon , 2 Tue...) To change it to 1 Sun, 2 Mon...we have to change only 1 digit of wx variable from 2 to 1 (LET row)
....wx,XMATCH(w,TEXT(SEQUENCE(7,,2),"ddd")).....to ...wx,XMATCH(w,TEXT(SEQUENCE(7,,1),"ddd"))....
Other functions on minisheet APIVOT . This is the modified lambda:
Excel Formula:
=LAMBDA(ar,t,cl,
    LET(c,MAX(1,cl),l,COLUMNS(ar),sl,SEQUENCE(,l),a,INDEX(ar,,c),d,DAY(a),m,MONTH(a),y,YEAR(a),w,TEXT(a,"ddd"),wx,XMATCH(w,TEXT(SEQUENCE(7,,2),"ddd")),
       r,y&"-",x,SWITCH(t,"m",TEXT(a,"yyy-mm-mmm"),"y",y,"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"),"ow",wx&" "&w),IF(OR(t={"od","m","y","q","e","f","om","oq","ow"}),
       IF(sl=c,x,ar),"check data")
    )
)
LAMBDA 7.0.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAG
1Challenge: Weekdays all time ranking of Revenues,Profit,Units sold values for each product,top to bottom(descending order)highest values,top
2lowest values, bottom
3DateNameProd.CostU.PrcU.SoldRevProfit=ADATE(A4:H53,"ow",)PT revenue(wkday\product)
424-04-21Antione GrinnellSunset913678246 SatAntione GrinnellSunset91367824(1\3) 7 vf=0CrestedMajesticQuadSunsetYanakiGrand TotalCrestedMajesticQuadSunsetYanakiGrand Total
525-12-20Freddie OttenMajestic81213156525 FriFreddie OttenMajestic81213156521 Mon70276040307492 Tue2 Tue5 Fri1 Mon2 Tue2 Tue
614-05-20Freddie OttenQuad71012120364 ThuFreddie OttenQuad71012120362 Tue3644323607859418287 Sun1 Mon7 Sun4 Thu7 Sun7 Sun
723-05-21Jesus PasquarielloSunset91311143447 SunJesus PasquarielloSunset91311143443 Wed0156120002765 Fri3 Wed2 Tue7 Sun6 Sat5 Fri
822-12-20Antione GrinnellSunset913678242 TueAntione GrinnellSunset913678244 Thu0012035104711 Mon5 Fri6 Sat2 Tue5 Fri1 Mon
919-03-21Antione GrinnellCrested514152101355 FriAntione GrinnellCrested514152101355 Fri2101564600558813 Wed7 Sun3 Wed6 Sat1 Mon4 Thu
1021-09-20Freddie OttenSunset913565201 MonFreddie OttenSunset913565206 Sat00150781543824 Thu4 Thu4 Thu3 Wed3 Wed6 Sat
1131-08-20Rey McglothlinMajestic812896321 MonRey McglothlinMajestic812896327 Sun23810846032516512966 Sat6 Sat1 Mon5 Fri4 Thu3 Wed
1210-11-20Theodore KrigerMajestic81212144482 TueTheodore KrigerMajestic8121214448Grand Total8821128167012359685883=SORTBY($S$5:$S$11,T5:T11,-1)
1321-09-20Antione GrinnellMajestic812672241 MonAntione GrinnellMajestic81267224=APIVOT(J4#,1,3,7,)
1425-12-20Houston YbarboQuad71013130395 FriHouston YbarboQuad7101313039
1511-05-21Rey McglothlinYanaki61112132602 TueRey McglothlinYanaki6111213260PT profit(wkday\product)
1628-05-21Antione GrinnellQuad710990275 FriAntione GrinnellQuad71099027(1\3) 8 vf=0CrestedMajesticQuadSunsetYanakiGrand TotalCrestedMajesticQuadSunsetYanakiGrand Total
1730-05-21Theodore KrigerQuad710550157 SunTheodore KrigerQuad710550151 Mon4592012402612 Tue2 Tue5 Fri1 Mon2 Tue2 Tue
1815-09-20Rey McglothlinCrested514121681082 TueRey McglothlinCrested514121681082 Tue234144108242707807 Sun1 Mon7 Sun4 Thu7 Sun7 Sun
1924-01-21Freddie OttenYanaki611666307 SunFreddie OttenYanaki611666303 Wed0523600885 Fri3 Wed2 Tue7 Sun6 Sat5 Fri
2016-03-21Mel ScheidlerMajestic812672242 TueMel ScheidlerMajestic812672244 Thu003610801441 Mon5 Fri6 Sat2 Tue5 Fri1 Mon
2124-01-21Jesus PasquarielloCrested514121681087 SunJesus PasquarielloCrested514121681085 Fri135521380253503 Wed7 Sun3 Wed6 Sat1 Mon4 Thu
2207-12-20Theodore KrigerSunset91313169521 MonTheodore KrigerSunset91313169526 Sat004524701394 Thu4 Thu4 Thu3 Wed3 Wed6 Sat
2324-04-21Houston YbarboYanaki61114154706 SatHouston YbarboYanaki61114154707 Sun15336138100755026 Sat6 Sat1 Mon5 Fri4 Thu3 Wed
2419-03-21Rey McglothlinQuad710770215 FriRey McglothlinQuad71077021Grand Total5673765013804402264=SORTBY($S$17:$S$23,T17:T23,-1)
2506-09-20Antione GrinnellQuad71015150457 SunAntione GrinnellQuad7101515045=APIVOT(J4#,1,3,8,)
2610-03-21Mel ScheidlerQuad71012120363 WedMel ScheidlerQuad7101212036
2714-10-20Jesus PasquarielloMajestic81213156523 WedJesus PasquarielloMajestic8121315652PT units sold(wkday\product)
2808-09-20Theodore KrigerQuad71015150452 TueTheodore KrigerQuad7101515045(1\3) 6 vf=0CrestedMajesticQuadSunsetYanakiGrand TotalCrestedMajesticQuadSunsetYanakiGrand Total
2923-06-20Antione ScheidlerYanaki61115165752 TueAntione ScheidlerYanaki61115165751 Mon5230310592 Tue2 Tue5 Fri1 Mon2 Tue2 Tue
3007-06-20Theodore KrigerSunset91314182567 SunTheodore KrigerSunset91314182562 Tue2636366541587 Sun1 Mon7 Sun4 Thu7 Sun7 Sun
3127-04-21Antione ScheidlerCrested514798632 TueAntione ScheidlerCrested514798633 Wed0131200255 Fri3 Wed2 Tue7 Sun6 Sat5 Fri
3211-05-21Rey McglothlinQuad71015150452 TueRey McglothlinQuad71015150454 Thu0012270391 Mon5 Fri6 Sat2 Tue5 Fri1 Mon
3307-12-20Houston YbarboCrested514570451 MonHouston YbarboCrested514570455 Fri15134605793 Wed7 Sun3 Wed6 Sat1 Mon4 Thu
3411-03-21Rey McglothlinSunset91312156484 ThuRey McglothlinSunset91312156486 Sat0015614354 Thu4 Thu4 Thu3 Wed3 Wed6 Sat
3524-01-21Mel ScheidlerCrested514570457 SunMel ScheidlerCrested514570457 Sun1794625151126 Sat6 Sat1 Mon5 Fri4 Thu3 Wed
3602-04-21Jesus PasquarielloQuad710990275 FriJesus PasquarielloQuad71099027Grand Total63941679588507=SORTBY($S$29:$S$35,T29:T35,-1)
3704-05-21Rey McglothlinYanaki611666302 TueRey McglothlinYanaki61166630=APIVOT(J4#,1,3,6,)
3810-11-20Houston YbarboYanaki61112132602 TueHouston YbarboYanaki6111213260
3928-11-20Mel ScheidlerQuad71015150456 SatMel ScheidlerQuad7101515045
4003-09-20Jesus PasquarielloSunset91315195604 ThuJesus PasquarielloSunset9131519560
4102-04-21Rey McglothlinYanaki611555255 FriRey McglothlinYanaki61155525
4220-12-20Freddie OttenMajestic8129108367 SunFreddie OttenMajestic812910836
4321-09-20Houston YbarboSunset91313169521 MonHouston YbarboSunset9131316952
4404-08-20Freddie OttenYanaki611999452 TueFreddie OttenYanaki61199945
4513-04-21Mel ScheidlerMajestic8129108362 TueMel ScheidlerMajestic812910836
4611-05-21Antione ScheidlerQuad710660182 TueAntione ScheidlerQuad71066018
4725-12-20Theodore KrigerQuad710880245 FriTheodore KrigerQuad71088024
4831-08-20Houston YbarboMajestic8129108361 MonHouston YbarboMajestic812910836
4923-03-21Freddie OttenMajestic8129108362 TueFreddie OttenMajestic812910836
5020-12-20Mel ScheidlerQuad71011110337 SunMel ScheidlerQuad7101111033
5122-12-20Antione GrinnellCrested514798632 TueAntione GrinnellCrested51479863
5230-05-21Freddie OttenQuad71015150457 SunFreddie OttenQuad7101515045
5330-05-21Antione GrinnellYanaki611999457 SunAntione GrinnellYanaki61199945
54
ADATE only wkday
Cell Formulas
RangeFormula
J3J3=FORMULATEXT(J4)
G4:G53G4=E4:E53*F4:F53
H4:H53H4=G4#-(D4:D53*F4:F53)
J4:Q53J4=ADATE(A4:H53,"ow",)
S4:Y12S4=APIVOT(J4#,1,3,7,)
AA5:AF11AA5=SORTBY($S$5:$S$11,T5:T11,-1)
AA12,AA36,AA24AA12=FORMULATEXT(AA5)
S13,S37,S25S13=FORMULATEXT(S4)
S16:Y24S16=APIVOT(J4#,1,3,8,)
AA17:AF23AA17=SORTBY($S$17:$S$23,T17:T23,-1)
S28:Y36S28=APIVOT(J4#,1,3,6,)
AA29:AF35AA29=SORTBY($S$29:$S$35,T29:T35,-1)
Dynamic array formulas.
 
Survey of all type arguments
LAMBDA 7.0.xlsx
ABCDEFGHIJKLMNOP
1=ADATE($F$3#,G2,)
2=RANDARRAY(40,,"1-jan-18",TODAY(),1) type arrayfemyqodomoqow
328-Feb-1801-02-1828-02-182018-02-Feb20182018-Q12802-FebQ13 Wed
4ADATE(ar,t,cl) 01-Sep-1801-09-1830-09-182018-09-Sep20182018-Q3109-SepQ36 Sat
5ar: array08-May-2101-05-2131-05-212021-05-May20212021-Q2805-MayQ26 Sat
6t: type15-Jun-1901-06-1930-06-192019-06-Jun20192019-Q21506-JunQ26 Sat
7cl: date column index05-May-1801-05-1831-05-182018-05-May20182018-Q2505-MayQ26 Sat
813-Oct-1801-10-1831-10-182018-10-Oct20182018-Q41310-OctQ46 Sat
9t (type) argumentdata typeformat14-Aug-1901-08-1931-08-192019-08-Aug20192019-Q31408-AugQ33 Wed
10ffirst of monthdate19-Feb-1801-02-1828-02-182018-02-Feb20182018-Q11902-FebQ11 Mon
11eend of monthdate03-Oct-1901-10-1931-10-192019-10-Oct20192019-Q4310-OctQ44 Thu
12mmonthtextyyyy-mm-mmm06-Jan-2001-01-2031-01-202020-01-Jan20202020-Q1601-JanQ11 Mon
13yyearnumeric11-May-2001-05-2031-05-202020-05-May20202020-Q21105-MayQ21 Mon
14qquartertextyyyy-Qn09-Aug-1801-08-1831-08-182018-08-Aug20182018-Q3908-AugQ34 Thu
15odonly datenumeric21-Nov-1901-11-1930-11-192019-11-Nov20192019-Q42111-NovQ44 Thu
16omonly monthtextmm-mmm16-Oct-2001-10-2031-10-202020-10-Oct20202020-Q41610-OctQ45 Fri
17oqonly quartertextQn14-Sep-2001-09-2030-09-202020-09-Sep20202020-Q31409-SepQ31 Mon
18owonly weekdaytext[wkday index] ddd27-May-2001-05-2031-05-202020-05-May20202020-Q22705-MayQ23 Wed
1922-Mar-1801-03-1831-03-182018-03-Mar20182018-Q12203-MarQ14 Thu
2004-Feb-1901-02-1928-02-192019-02-Feb20192019-Q1402-FebQ11 Mon
2116-Jun-1801-06-1830-06-182018-06-Jun20182018-Q21606-JunQ26 Sat
2230-Jun-1801-06-1830-06-182018-06-Jun20182018-Q23006-JunQ26 Sat
2304-May-2101-05-2131-05-212021-05-May20212021-Q2405-MayQ22 Tue
2414-Apr-1901-04-1930-04-192019-04-Apr20192019-Q21404-AprQ27 Sun
2529-Nov-1801-11-1830-11-182018-11-Nov20182018-Q42911-NovQ44 Thu
2605-Apr-1901-04-1930-04-192019-04-Apr20192019-Q2504-AprQ25 Fri
2701-Aug-2001-08-2031-08-202020-08-Aug20202020-Q3108-AugQ36 Sat
2804-Aug-2001-08-2031-08-202020-08-Aug20202020-Q3408-AugQ32 Tue
2930-Aug-1801-08-1831-08-182018-08-Aug20182018-Q33008-AugQ34 Thu
3007-Jan-1801-01-1831-01-182018-01-Jan20182018-Q1701-JanQ17 Sun
3103-May-2001-05-2031-05-202020-05-May20202020-Q2305-MayQ27 Sun
3227-Jan-1901-01-1931-01-192019-01-Jan20192019-Q12701-JanQ17 Sun
3302-Feb-1901-02-1928-02-192019-02-Feb20192019-Q1202-FebQ16 Sat
3430-May-2001-05-2031-05-202020-05-May20202020-Q23005-MayQ26 Sat
3512-Oct-2001-10-2031-10-202020-10-Oct20202020-Q41210-OctQ41 Mon
3610-Oct-2001-10-2031-10-202020-10-Oct20202020-Q41010-OctQ46 Sat
3708-Apr-1801-04-1830-04-182018-04-Apr20182018-Q2804-AprQ27 Sun
3803-Apr-2101-04-2130-04-212021-04-Apr20212021-Q2304-AprQ26 Sat
3911-Dec-1801-12-1831-12-182018-12-Dec20182018-Q41112-DecQ42 Tue
4031-Mar-1901-03-1931-03-192019-03-Mar20192019-Q13103-MarQ17 Sun
4110-Jul-2001-07-2031-07-202020-07-Jul20202020-Q31007-JulQ35 Fri
4218-Oct-1801-10-1831-10-182018-10-Oct20182018-Q41810-OctQ44 Thu
43
ADATE new
Cell Formulas
RangeFormula
G1G1=FORMULATEXT(G3)
A2A2=FORMULATEXT(F3)
F3:F42F3=RANDARRAY(40,,"1-jan-18",TODAY(),1)
G3:O42G3=ADATE($F$3#,G2,)
Dynamic array formulas.
 
Happy 4th of July !!!
To celebrate, here is a function to help us find dates based on any weekday occurrence pattern, like 2nd Monday after a certain date or of a certain month etc.
Or find n'th Sunday before or after a certain date that can be any date, the beginning, or the end of the current month of that date.
Inspired by: Find the Last Sunday Of the Month
Important note: The function does not use WEEKDAY function at all. return_type argument can mess up the results if not used with care. WEEKDAY can be used only when sorting is involved.
Find Weekday Function
FNDWDY(da,[wd],[nr],[cm],[df])
da:dates array, any array (starting date reference)
[wd]: weekday, string 3 letters; mon or MON or Mon for Monday etc.
- if omitted, wd value will be the weekday of respective date delivered by date array "da"
[nr]: the nr'th occurrence of "wd"; if nr<0, before the date; if nr>0 after the date
- if omitted => nr=1 => first occurrence after the date
[cm]: current month argument
- if omitted or 0 starting date will be considered the date delivered by "da"
- if 1 or <>0, if nr>0, starting date will be considered first day of current month ; if nr<0, starting date will be considered the end of current month
[df]: date format
- if omitted, date result returned in this format "Mon 04-05-2022" or "*Mon 04-05-2022" (text format)
The "*" represents that the result's month is different to the current month
- if 1, date numeric format

Excel Formula:
=LAMBDA(da, [wd], [nr], [cm], [df],
    LET(
        m, MAP(
            da,
            LAMBDA(x,
                LET(
                    n, IF(nr, nr, 1),
                    g, SIGN(n),
                    r, n * g,
                    f, DATE(YEAR(x), MONTH(x), 1),
                    e, EOMONTH(f, 0),
                    s, SWITCH(
                        wd,
                        1,
                        f,
                        2,
                        e,
                        LET(
                            b, IF(cm, IF(g = -1, e, f), x),
                            ad, SEQUENCE(7 * (r - 1) + 7, , b, g),
                            w, IF(wd = 0, TEXT(x, "ddd"), wd),
                            INDEX(FILTER(ad, TEXT(ad, "ddd") = w), r)
                        )
                    ),
                    t, IF(df, s, TEXT(s, "ddd dd-mm-yyyy")),
                    IF(MONTH(s) = MONTH(x), t, IF(df, t, "*" & t))
                )
            )
        ),
        IF(da = "", "", IFERROR(m, ""))
    )
)
ADATE.xlsx
ABCDEFGHIJKLMNOPQR
1First, find weekday of 4'th July (also simple TEXT will do)
2Holidays in US that require weekday calculationsweekday first of monthweekday end of month
3all arg omittedda=FNDWDY(I4:I15)=FNDWDY(EOMONTH(+I4:I15,0))
4Holidayfind pattern=FNDWDY(E5:E15)01-01-22Sat 01-01-2022Mon 31-01-2022
51Martin Luther King Jr.3rd Mon Janda04-07-20Sat 04-07-202001-02-22Tue 01-02-2022Mon 28-02-2022
62Washington's Birthday3rd Mon Feb04-07-21Sun 04-07-202101-03-22Tue 01-03-2022Thu 31-03-2022
73Memorial Daylast Mon May04-07-22Mon 04-07-202201-04-22Fri 01-04-2022Sat 30-04-2022
84Labor Day1st Mon Sep04-07-23Tue 04-07-202301-05-22Sun 01-05-2022Tue 31-05-2022
95Columbus Day2nd Mon Oct04-07-24Thu 04-07-202401-06-22Wed 01-06-2022Thu 30-06-2022
106Thanksgiving day4th Thu Nov04-07-25Fri 04-07-202501-07-22Fri 01-07-2022Sun 31-07-2022
117President's day3rd Mon Feb04-07-26Sat 04-07-202601-08-22Mon 01-08-2022Wed 31-08-2022
128Mother's day2nd Sun May04-07-27Sun 04-07-202701-09-22Thu 01-09-2022Fri 30-09-2022
139Black Fridayday after TKG04-07-28Tue 04-07-202801-10-22Sat 01-10-2022Mon 31-10-2022
1410Cyber Monday1 Mon after TKG04-07-29Wed 04-07-202901-11-22Tue 01-11-2022Wed 30-11-2022
1504-07-30Thu 04-07-203001-12-22Thu 01-12-2022Sat 31-12-2022
16TKG-Thanksgiving date
17
181. MLK Jr. - 3rd Mon Janif any date of Jan we can use cm=1 argumentif cm omitted, wrong results
19ignores current day, counts from 1st of monthcm,1 and nr=3 (>0) => start date for sure will be beginning of month
20=FNDWDY(E21:E31,"mon",3)da=FNDWDY(I21:I31,"mon",3,1)=FNDWDY(I21:I31,"mon",3)
21da01-01-20Mon 20-01-202012-01-20Mon 20-01-2020Mon 27-01-2020
2201-01-21Mon 18-01-202107-01-21Mon 18-01-2021Mon 25-01-2021
2301-01-22Mon 17-01-202218-01-22Mon 17-01-2022*Mon 07-02-2022
2401-01-23Mon 16-01-202302-01-23Mon 16-01-2023Mon 16-01-2023
2501-01-24Mon 15-01-202411-01-24Mon 15-01-2024Mon 29-01-2024
2601-01-25Mon 20-01-202531-01-25Mon 20-01-2025*Mon 17-02-2025
2701-01-26Mon 19-01-202619-01-26Mon 19-01-2026*Mon 02-02-2026
2801-01-27Mon 18-01-202709-01-27Mon 18-01-2027Mon 25-01-2027
2901-01-28Mon 17-01-202827-01-28Mon 17-01-2028*Mon 14-02-2028
3001-01-29Mon 15-01-202923-01-29Mon 15-01-2029*Mon 12-02-2029
3101-01-30Mon 21-01-203014-01-30Mon 21-01-2030Mon 28-01-2030
32
FNDWDY
Cell Formulas
RangeFormula
M3,K20,M20,G20,K3,G4M3=FORMULATEXT(M4)
K4:K15K4=FNDWDY(I4:I15)
M4:M15M4=FNDWDY(EOMONTH(+I4:I15,0))
G5:G15G5=FNDWDY(E5:E15)
G21:G31G21=FNDWDY(E21:E31,"mon",3)
K21:K31K21=FNDWDY(I21:I31,"mon",3,1)
M21:M31M21=FNDWDY(I21:I31,"mon",3)
Dynamic array formulas.
 
ADATE.xlsx
ABCDEFGHIJKLMNOP
1
2Last Sun of month2. Washington's Birthday - 3rd Mon Feb3. Memorial Day - last Mon May
3da,2D array=DATE(SEQUENCE(7,,2020),2,1)=DATE(SEQUENCE(7,,2020),SEQUENCE(7,,5,0),1)
404-07-2212-01-23↓↓↓=FNDWDY(E5#,"Mon",3)=FNDWDY(I5#,"mon",-1,1)
518-08-22abcd01-02-20Mon 17-02-202001-05-20Mon 25-05-2020
604-09-2224-02-2301-02-21Mon 15-02-202101-05-21Mon 31-05-2021
723-10-2201-02-22Mon 21-02-202201-05-22Mon 30-05-2022
829-11-2208-03-2301-02-23Mon 20-02-202301-05-23Mon 29-05-2023
913-12-2217-04-2301-02-24Mon 19-02-202401-05-24Mon 27-05-2024
1001-02-25Mon 17-02-202501-05-25Mon 26-05-2025
11=FNDWDY(B4:C9,"sun",-1,1)01-02-26Mon 16-02-202601-05-26Mon 25-05-2026
12Sun 31-07-2022Sun 29-01-2023
13Sun 28-08-2022Creating random dates of nov for a sequence of years6. Thanksgiving day - 4th Thu Nov
14Sun 25-09-2022Sun 26-02-2023=DATE(SEQUENCE(7,,2020),11,RANDBETWEEN(1,30))
15Sun 30-10-2022↓↓↓=FNDWDY(I16#,"thu",4,1)
16Sun 27-11-2022Sun 26-03-202304-11-2019-11-20Thu 26-11-2020
17Sun 25-12-2022Sun 30-04-202304-11-2121-11-21Thu 25-11-2021
1804-11-2202-11-22Thu 24-11-2022
192 Sun before starting date04-11-2329-11-23Thu 23-11-2023
20=FNDWDY(B4:C9,"sun",-2)04-11-2414-11-24Thu 28-11-2024
21*Sun 26-06-2022Sun 01-01-202304-11-2517-11-25Thu 27-11-2025
22Sun 07-08-202204-11-2623-11-26Thu 26-11-2026
23*Sun 28-08-2022Sun 12-02-2023I16:
24Sun 16-10-2022formula E16 produces same days,=ATEXTSPILL(SEQUENCE(7),LAMBDA(x,DATE(2019+x,11,RANDBETWEEN(1,30))))
25Sun 20-11-2022*Sun 26-02-2023does not recalculate / each row =>with ATEXTSPILL we can creade random days
26Sun 04-12-2022Sun 09-04-2023
279. Black Friday - day after TKG
28random days Nov
29other functions=ATEXTSPILL(SEQUENCE(7),LAMBDA(x,DATE(2019+x,11,RANDBETWEEN(1,30))))
30ATEXTSPILL↓↓↓df,1 => dates in numeric format, we can add 1
31↓↓↓=FNDWDY(E32#,"Thu",4,1,1)+1
3217-11-2027-11-20
3324-11-2126-11-2110. Cyber Monday - 1 Mon after TKG, 2 ways with nested FNDWDY
3422-11-2225-11-22random days Nov
3509-11-2324-11-23=ATEXTSPILL(SEQUENCE(7),LAMBDA(x,DATE(2019+x,11,RANDBETWEEN(1,30))))
3602-11-2429-11-24↓↓↓=FNDWDY(I37#,"thu",4,1,1)+4=FNDWDY(FNDWDY(I37#,"thu",4,1,1),"mon")
3715-11-2528-11-2503-11-2030-11-20Mon 30-11-2020
3805-11-2627-11-2614-11-2129-11-21Mon 29-11-2021
3928-11-2228-11-22Mon 28-11-2022
4030-11-2327-11-23Mon 27-11-2023
4101-11-2402-12-24*Mon 02-12-2024
4219-11-2501-12-25*Mon 01-12-2025
4323-11-2630-11-26Mon 30-11-2026
44
45"*" => Cyber Monday not in Nov
46
FNDWDY 2
Cell Formulas
RangeFormula
E3,I35,E14,I3E3=FORMULATEXT(E5)
G4,K36,M36,G31,B20,K15,B11,K4G4=FORMULATEXT(G5)
E5:E11E5=DATE(SEQUENCE(7,,2020),2,1)
G5:G11G5=FNDWDY(E5#,"Mon",3)
I5:I11I5=DATE(SEQUENCE(7,,2020),SEQUENCE(7,,5,0),1)
K5:K11K5=FNDWDY(I5#,"mon",-1,1)
B12:C17B12=FNDWDY(B4:C9,"sun",-1,1)
E16:E22E16=DATE(SEQUENCE(7,,2020),11,RANDBETWEEN(1,30))
I16:I22,I37:I43,E32:E38I16=ATEXTSPILL(SEQUENCE(7),LAMBDA(x,DATE(2019+x,11,RANDBETWEEN(1,30))))
K16:K22K16=FNDWDY(I16#,"thu",4,1)
B21:C26B21=FNDWDY(B4:C9,"sun",-2)
H24H24=FORMULATEXT(I16)
E29E29=FORMULATEXT(E32)
G32:G38G32=FNDWDY(E32#,"Thu",4,1,1)+1
K37:K43K37=FNDWDY(I37#,"thu",4,1,1)+4
M37:M43M37=FNDWDY(FNDWDY(I37#,"thu",4,1,1),"mon")
Dynamic array formulas.
 
Due date example.
ADATE.xlsx
ABCDEFGHIJKLMNOP
1Task. Calculating due datesingle cell
2Rules:If invoice issued from Mon to Wed due date 3 weeks from Wedlambda formula
3If invoice issued from Thu to Sun due date 4 weeks from WedJ8:=LET(a,B8#,w,WEEKDAY(a,2),MAP(a,w,LAMBDA(x,y,FNDWDY(x,"wed",IF(y<3,3,4),,1))))
4↓↓↓
5=IF(D8#<3,FNDWDY(B8#,"wed",3),FNDWDY(B8#,"wed",4))↓↓↓
6=RANDARRAY(50,,"5-7-22","31-12-22",1)↓↓↓or=MAP(B8#,D8#,LAMBDA(x,y,FNDWDY(x,"wed",IF(y<3,3,4))))
7Inv Date=WEEKDAY(B8#,2)↓↓↓↓↓↓Due date (numeric date format)
815-08-221Wed 31-08-2022Wed 31-08-202231-08-22
912-09-221Wed 28-09-2022Wed 28-09-202228-09-22
1019-08-225*Wed 14-09-2022*Wed 14-09-202214-09-22
1112-09-221Wed 28-09-2022Wed 28-09-202228-09-22
1227-12-222*Wed 11-01-2023*Wed 11-01-202311-01-23
1310-11-224*Wed 07-12-2022*Wed 07-12-202207-12-22
1407-08-227Wed 31-08-2022Wed 31-08-202231-08-22
1529-07-225*Wed 24-08-2022*Wed 24-08-202224-08-22
1629-07-225*Wed 24-08-2022*Wed 24-08-202224-08-22
1721-08-227*Wed 14-09-2022*Wed 14-09-202214-09-22
1825-07-221*Wed 10-08-2022*Wed 10-08-202210-08-22
1906-10-224*Wed 02-11-2022*Wed 02-11-202202-11-22
2007-09-223Wed 28-09-2022Wed 28-09-202228-09-22
2130-10-227*Wed 23-11-2022*Wed 23-11-202223-11-22
2223-10-227*Wed 16-11-2022*Wed 16-11-202216-11-22
2306-12-222Wed 21-12-2022Wed 21-12-202221-12-22
2426-11-226*Wed 21-12-2022*Wed 21-12-202221-12-22
2512-10-223*Wed 02-11-2022*Wed 02-11-202202-11-22
2627-07-223*Wed 17-08-2022*Wed 17-08-202217-08-22
2704-10-222Wed 19-10-2022Wed 19-10-202219-10-22
2823-11-223*Wed 14-12-2022*Wed 14-12-202214-12-22
2918-11-225*Wed 14-12-2022*Wed 14-12-202214-12-22
3017-07-227*Wed 10-08-2022*Wed 10-08-202210-08-22
3130-09-225*Wed 26-10-2022*Wed 26-10-202226-10-22
3208-09-224*Wed 05-10-2022*Wed 05-10-202205-10-22
3307-08-227Wed 31-08-2022Wed 31-08-202231-08-22
3428-12-223*Wed 18-01-2023*Wed 18-01-202318-01-23
3503-09-226Wed 28-09-2022Wed 28-09-202228-09-22
3624-12-226*Wed 18-01-2023*Wed 18-01-202318-01-23
3727-09-222*Wed 12-10-2022*Wed 12-10-202212-10-22
3815-07-225*Wed 10-08-2022*Wed 10-08-202210-08-22
3924-11-224*Wed 21-12-2022*Wed 21-12-202221-12-22
4029-07-225*Wed 24-08-2022*Wed 24-08-202224-08-22
4105-10-223Wed 26-10-2022Wed 26-10-202226-10-22
4218-07-221*Wed 03-08-2022*Wed 03-08-202203-08-22
4309-08-222Wed 24-08-2022Wed 24-08-202224-08-22
4424-09-226*Wed 19-10-2022*Wed 19-10-202219-10-22
4531-12-226*Wed 25-01-2023*Wed 25-01-202325-01-23
4620-09-222*Wed 05-10-2022*Wed 05-10-202205-10-22
4718-12-227*Wed 11-01-2023*Wed 11-01-202311-01-23
4822-09-224*Wed 19-10-2022*Wed 19-10-202219-10-22
4901-11-222Wed 16-11-2022Wed 16-11-202216-11-22
5012-08-225*Wed 07-09-2022*Wed 07-09-202207-09-22
5115-10-226*Wed 09-11-2022*Wed 09-11-202209-11-22
5213-11-227*Wed 07-12-2022*Wed 07-12-202207-12-22
5321-07-224*Wed 17-08-2022*Wed 17-08-202217-08-22
5408-07-225*Wed 03-08-2022*Wed 03-08-202203-08-22
5509-09-225*Wed 05-10-2022*Wed 05-10-202205-10-22
5620-09-222*Wed 05-10-2022*Wed 05-10-202205-10-22
5730-09-225*Wed 26-10-2022*Wed 26-10-202226-10-22
58
Due Date
Cell Formulas
RangeFormula
J3J3=FORMULATEXT(J8)
F5F5=FORMULATEXT(F8)
B6,H6B6=FORMULATEXT(B8)
D7D7=FORMULATEXT(D8)
B8:B57B8=RANDARRAY(50,,"5-7-22","31-12-22",1)
D8:D57D8=WEEKDAY(B8#,2)
F8:F57F8=IF(D8#<3,FNDWDY(B8#,"wed",3),FNDWDY(B8#,"wed",4))
H8:H57H8=MAP(B8#,D8#,LAMBDA(x,y,FNDWDY(x,"wed",IF(y<3,3,4))))
J8:J57J8=LET(a,B8#,w,WEEKDAY(a,2),MAP(a,w,LAMBDA(x,y,FNDWDY(x,"wed",IF(y<3,3,4),,1))))
Dynamic array formulas.
 
Epic video, latest, Mike's YT Date & Time Formulas & Functions in Excel Worksheet and Power Query - 365 MECS 05
At min 38.07, about quarters, fiscal years, formulas and PQ
Covered before a quarter function APIVOT post #17
QTR(ar,[qt],[sm]) (added error management and new argument)
ar: dates array, (could be single value, cell reference, column/row vectors or even 2D arrays)
[qt]: quarter type argument, (one of these values "q", "qy", fq", "fy", "fqy" )
[sm]: start month of fiscal year - if sm is omitted, default value is 4, if we need 7 we input 7
qt quarter type argument values:
- if qt is omitted, default value = "qy" => this format ex: 2022 Q4 (always year first, for sorting versatility)
"q" ,calendar quarter => format ex: Q4
"qy" ,calendar quarter and year => format ex: 2022 Q4
"fq" ,fiscal quarter => format ex: FQ4
"fy" ,fiscal year => format ex: FY 2023 (fiscal year 2023-24)
"fqy" ,fiscal quarter and year => format ex: FY 2023 Q4
If none of the above, the function will return the value for "fqy"
Could have used numeric values instead of text, but with letters it is easier to remember.
Excel Formula:
=LAMBDA(ar, [qt], [sm],
    LET(
        s, IF(sm, sm, 4),
        a, IF(ar = "", "", ar),
        m, MONTH(a),
        y, YEAR(a),
        q, "Q" & MONTH(m & 0),
        qy, y & " " & q,
        fq, "Q" & MONTH(MOD(m - s, 12) + 1 & 0),
        fy, "FY " & y + (m >= s) - 1,
        fqy, fy & " " & fq,
        IFERROR(SWITCH(qt, 0, qy, "q", q, "qy", qy, "fq", "F" & fq, "fy", fy, "fqy", fqy),"")
    )
)
05-M365ExcelClass.xlsx
ABCDEFGHIJKLMNOPQRST
1April 1 is 1st day in Fiscal YearMike's data
2Date Worksheet Formulas to Create Data Attributes for a Table of Sales Data
3Get Year, Month, Day and Quarter Data from a Serial Number:=QTR(S[Date],"qy")=QTR(S[Date],"fy")
4DateYEARMONTHQuarterQ LabelFiscal QuarterFiscal YearF Q Label=QTR(S[Date],"q")↓↓↓=QTR(S[Date],"fq")↓↓↓=QTR(S[Date],"fqy")
519-04-23202342Q - 2120232023 - Q 1Q22023 Q2FQ1FY 2023FY 2023 Q1
626-01-23202311Q - 1420222022 - Q 4Q12023 Q1FQ4FY 2022FY 2022 Q4
728-02-23202321Q - 1420222022 - Q 4Q12023 Q1FQ4FY 2022FY 2022 Q4
818-10-222022104Q - 4320222022 - Q 3Q42022 Q4FQ3FY 2022FY 2022 Q3
927-02-22202221Q - 1420212021 - Q 4Q12022 Q1FQ4FY 2021FY 2021 Q4
1007-04-22202242Q - 2120222022 - Q 1Q22022 Q2FQ1FY 2022FY 2022 Q1
1124-12-222022124Q - 4320222022 - Q 3Q42022 Q4FQ3FY 2022FY 2022 Q3
1205-05-22202252Q - 2120222022 - Q 1Q22022 Q2FQ1FY 2022FY 2022 Q1
1321-02-22202221Q - 1420212021 - Q 4Q12022 Q1FQ4FY 2021FY 2021 Q4
1401-07-23202373Q - 3220232023 - Q 2Q32023 Q3FQ2FY 2023FY 2023 Q2
1531-01-22202211Q - 1420212021 - Q 4Q12022 Q1FQ4FY 2021FY 2021 Q4
1615-03-23202331Q - 1420222022 - Q 4Q12023 Q1FQ4FY 2022FY 2022 Q4
1723-11-222022114Q - 4320222022 - Q 3Q42022 Q4FQ3FY 2022FY 2022 Q3
1827-12-222022124Q - 4320222022 - Q 3Q42022 Q4FQ3FY 2022FY 2022 Q3
1915-11-222022114Q - 4320222022 - Q 3Q42022 Q4FQ3FY 2022FY 2022 Q3
2023-06-23202362Q - 2120232023 - Q 1Q22023 Q2FQ1FY 2023FY 2023 Q1
2110-09-23202393Q - 3220232023 - Q 2Q32023 Q3FQ2FY 2023FY 2023 Q2
2226-02-22202221Q - 1420212021 - Q 4Q12022 Q1FQ4FY 2021FY 2021 Q4
2317-03-23202331Q - 1420222022 - Q 4Q12023 Q1FQ4FY 2022FY 2022 Q4
2416-12-222022124Q - 4320222022 - Q 3Q42022 Q4FQ3FY 2022FY 2022 Q3
2531-01-23202311Q - 1420222022 - Q 4Q12023 Q1FQ4FY 2022FY 2022 Q4
2610-03-23202331Q - 1420222022 - Q 4Q12023 Q1FQ4FY 2022FY 2022 Q4
2706-12-222022124Q - 4320222022 - Q 3Q42022 Q4FQ3FY 2022FY 2022 Q3
2808-07-23202373Q - 3220232023 - Q 2Q32023 Q3FQ2FY 2023FY 2023 Q2
2921-04-22202242Q - 2120222022 - Q 1Q22022 Q2FQ1FY 2022FY 2022 Q1
3022-06-22202262Q - 2120222022 - Q 1Q22022 Q2FQ1FY 2022FY 2022 Q1
3126-04-22202242Q - 2120222022 - Q 1Q22022 Q2FQ1FY 2022FY 2022 Q1
3204-12-232023124Q - 4320232023 - Q 3Q42023 Q4FQ3FY 2023FY 2023 Q3
3317-05-22202252Q - 2120222022 - Q 1Q22022 Q2FQ1FY 2022FY 2022 Q1
3418-06-22202262Q - 2120222022 - Q 1Q22022 Q2FQ1FY 2022FY 2022 Q1
3511-11-222022114Q - 4320222022 - Q 3Q42022 Q4FQ3FY 2022FY 2022 Q3
3613-11-222022114Q - 4320222022 - Q 3Q42022 Q4FQ3FY 2022FY 2022 Q3
3723-05-22202252Q - 2120222022 - Q 1Q22022 Q2FQ1FY 2022FY 2022 Q1
3819-01-22202211Q - 1420212021 - Q 4Q12022 Q1FQ4FY 2021FY 2021 Q4
3911-08-22202283Q - 3220222022 - Q 2Q32022 Q3FQ2FY 2022FY 2022 Q2
4017-01-23202311Q - 1420222022 - Q 4Q12023 Q1FQ4FY 2022FY 2022 Q4
4124-02-22202221Q - 1420212021 - Q 4Q12022 Q1FQ4FY 2021FY 2021 Q4
4212-02-23202321Q - 1420222022 - Q 4Q12023 Q1FQ4FY 2022FY 2022 Q4
4311-06-22202262Q - 2120222022 - Q 1Q22022 Q2FQ1FY 2022FY 2022 Q1
4413-07-23202373Q - 3220232023 - Q 2Q32023 Q3FQ2FY 2023FY 2023 Q2
DateFields (an)
Cell Formulas
RangeFormula
M3,Q3M3=FORMULATEXT(M5)
K4,S4,O4K4=FORMULATEXT(K5)
C5:C44C5=YEAR([@Date])
D5:D44D5=MONTH([@Date])
E5:E44E5=ROUNDUP([@MONTH]/3,0)
F5:F44F5="Q - "&[@Quarter]
G5:G44G5=IF([@MONTH]<4,4,[@Quarter]-1)
H5:H44H5=IF([@MONTH]<4,[@YEAR]-1,[@YEAR])
I5:I44I5=[@[Fiscal Year]]&" - Q "&[@[Fiscal Quarter]]
K5:K147K5=QTR(S[Date],"q")
M5:M147M5=QTR(S[Date],"qy")
O5:O147O5=QTR(S[Date],"fq")
Q5:Q147Q5=QTR(S[Date],"fy")
S5:S147S5=QTR(S[Date],"fqy")
Dynamic array formulas.
 
A very cool challenge emerged from the comment section same YT from Mark Thompson:
".... One topic eludes me though… I do archival work on old collections for family history research, and I am constantly tripped up when working with dates before 1900.
Do you have, or know of a good video/blog for effectively dealing with dates prior to 1900 in either Excel or PQ? As they always format as strings, I can’t do date math or sorts with them, especially when they are included with other dates after 1900. Surely someone else has solved this… or, I’m missing something easy? Everything I’ve come up with so far is a messy hack."

I had several approaches to solve this, this is, I think, the best one: Continuing the sequence of dates serial numbers for dates before 1Jan1900 with negative numbers.

First, about leap years. Quotes from List of leap years and leap days since 1600 and from Wikipedia.
"Every year that is exactly divisible by four is a leap year, except for years that are exactly divisible by 100, but these centurial years are leap years if they are exactly divisible by 400. For example, the years 1700, 1800, and 1900 are not leap years, but the years 1600 and 2000 are."
"The Gregorian calendar (introduced in Oct 1582) omits three leap days every 400 years, which is the length of its leap cycle. This is done by omitting February 29 in the three century years (multiples of 100) that are not multiples of 400. The years 2000 and 2400 are leap years, but not 1700, 1800, 1900, 2100, 2200 and 2300"

Excel "anomaly": Excel calculates the year 1900 as a leap year but it wasn't. Excel incorrectly assumes that the year 1900 is a leap year
Because of this, weekdays before 29Feb1990 are offset by 1 day => =TEXT("1-1-1900","ddd") returns Sunday instead of Monday.
If 1Jan1900 was a Monday, then, a day before that, 31Dec1899, was a Sunday.
Starting with1Mar1900, weekdays align.
NDT(y,m,d) the Negative DaTe function. Same arguments as DATE function.
y: year value or years array; 1600<=y< 9999
m: month value or months array
d: day value or days array
Excel Formula:
=LAMBDA(y, m, d,
    IF(y >= 1900, DATE(y, m, d), IF(y < 1600, NA(), DATE(y + 400, m, d) - 146098))
)
Neg sn dates.xlsx
ABCDEFGHIJKLMNOPQRSTUV
1NDT(y,m,d) Concept,Negative dates/sn ConceptConcept of symmetric leap years distributions (1600 to 1899 vs 2000 to 2299)
2Array of dates variables in descending order 1600-18992000-2299
3back in the past to 1Jan1600=LET(a,SEQUENCE(300,,1600),FILTER(a,(MOD(a,4)=0)*(a<>1700)*(a<>1800)))
4excelreal↓↓↓=LET(a,SEQUENCE(300,,2000),FILTER(a,(MOD(a,4)=0)*(a<>2100)*(a<>2200)))
5YearMonthDaysnwkdywkdy16002000check symmetry
622991231146098SunSun┐―16042004=UNIQUE(N5#-L5#)
722991230146097SatSat16082008400
8 Ͱ 10957316122012If leap years distribution is symmetric
9 - - - - - -16162016means that distribution of months and days is identical.
1020001236527SunSun16202020=> days btw 1Jan2000 and 31Dec2299 =
1120001136526SatSat16242024 =days btw 1Jan1600 and 31Dec1899 =
12 - - - - - -│ days gap16282028="31-12-2299"-"1-1-2000"+1
13 - - - - - - Ͱ 14609816322032109573
1419003161ThuThu16362036"?" sn calculation sn(1Jan1600)
15190022960Wedx164020400-"?"+1=109573
16190022859TueWed16442044?=> "?" =sn(1Jan1600)= - 109572
17---- - -16482048
18---- - -16522052Years gap 2000-1600=400 years
191900122MonTue16562056=2000-1600
201900111SunMon16602060400
21189912310(Sat)Sun┐―16642064Days gap =DATE(2000,1,1)-sn(1Jan1600)
2218991230-1Sat16682068=DATE(2000,1,1)-(-109572)
2318991229-2Fri16722072146098
24----- Ͱ 10957316762076
25-----16802080core expresion of NDT function
26160012--16842084Serial number (sn) of a date before 1900 (1600<=y<1900)
27160011??16882088 =ndt(y,m,d)=date(y+400,m,d)-146098
2816922092
2916962096Weekdays distribution.
3017042104wkdy of 1Jan1990 (sn=1)
3117082108=TEXT("1-1-1900","ddd")
3217122112Sun=> in reality was a Mon
3317162116=> for sn=0 (31Dec1899) weekday was Sun
3417202120400 years after 31Dec1899 => 31Dec2299
3517242124=TEXT("31-12-2299","ddd")
3617282128Sun=> also weekdays distribution is identical
3717322132 => wkdy(1Jan1600)=wkdy(1Jan1600+400)
3817362136=TEXT("1-1-2000","ddd")
3917402140?Sat
4017442144
4117482148
4217522152
4317562156
4417602160
4517642164
4617682168
4717722172
4817762176
4917802180
5017842184
5117882188
5217922192
5317962196
5418042204
5518082208
5618122212
5718162216
5818202220
5918242224
6018282228
6118322232
6218362236
6318402240
6418442244
6518482248
6618522252
6718562256
6818602260
6918642264
7018682268
7118722272
7218762276
7318802280
7418842284
7518882288
7618922292
7718962296
78
Concept
Cell Formulas
RangeFormula
L3L3=FORMULATEXT(L5)
N4,Q38,Q35,Q31,Q22,Q19,Q12,Q6N4=FORMULATEXT(N5)
L5:L77L5=LET(a,SEQUENCE(300,,1600),FILTER(a,(MOD(a,4)=0)*(a<>1700)*(a<>1800)))
N5:N77N5=LET(a,SEQUENCE(300,,2000),FILTER(a,(MOD(a,4)=0)*(a<>2100)*(a<>2200)))
Q7Q7=UNIQUE(N5#-L5#)
Q13Q13="31-12-2299"-"1-1-2000"+1
Q20Q20=2000-1600
Q23Q23=DATE(2000,1,1)-(-109572)
Q32Q32=TEXT("1-1-1900","ddd")
Q36Q36=TEXT("31-12-2299","ddd")
Q39Q39=TEXT("1-1-2000","ddd")
Dynamic array formulas.
 
Neg sn dates.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAA
1The function NDT(y,m,d)(like DATE function, NDT can handle array arguments)
2=NDT(B5:B18,C5:C18,D5:D18)
3↓↓↓1st of every month for each yearserial numbers of 1st of Apr for years interval 1880-1910
4YearMonthDaysn↓↓↓=NDT(B5:B18,SEQUENCE(,12),1)=NDT(SEQUENCE(31,,1880),4,1)
522991231146098146098145734145765145793145824145854145885145915145946145977146007146038146068-7213
622991230146097146097145734145765145793145824145854145885145915145946145977146007146038146068-6848
72000123652736527365263655736586366173664736678367083673936770368003683136861-6483
82000113652636526365263655736586366173664736678367083673936770368003683136861-6118
919003161611326192122153183214245275306336-5752
10190022960601326192122153183214245275306336-5387
11190022859591326192122153183214245275306336-5022
12190012221326192122153183214245275306336-4657
13190011111326192122153183214245275306336-4291
141899123100-364-333-305-274-244-213-183-152-121-91-60-30-3926
1518991230-1-1-364-333-305-274-244-213-183-152-121-91-60-30-3561
1618991229-2-2-364-333-305-274-244-213-183-152-121-91-60-30-3196
17160012-109571-109572-109541-109512-109481-109451-109420-109390-109359-109328-109298-109267-109237-2830
18160011-109572-109572-109541-109512-109481-109451-109420-109390-109359-109328-109298-109267-109237-2465
19-2100
20first 10 days of Dec for each year-1735
21=NDT(B5:B18,12,SEQUENCE(,10))-1369
22146068146069146070146071146072146073146074146075146076146077-1004
23146068146069146070146071146072146073146074146075146076146077-639
2436861368623686336864368653686636867368683686936870-274
253686136862368633686436865368663686736868368693687092
26336337338339340341342343344345457
27336337338339340341342343344345822
283363373383393403413423433443451187
293363373383393403413423433443451553
303363373383393403413423433443451918
31-30-29-28-27-26-25-24-23-22-212283
32-30-29-28-27-26-25-24-23-22-212648
33-30-29-28-27-26-25-24-23-22-213014
34-109237-109236-109235-109234-109233-109232-109231-109230-109229-1092283379
35-109237-109236-109235-109234-109233-109232-109231-109230-109229-1092283744
36
NDT
Cell Formulas
RangeFormula
G2G2=FORMULATEXT(G5)
I4,I21,V4I4=FORMULATEXT(I5)
G5:G18G5=NDT(B5:B18,C5:C18,D5:D18)
I5:T18I5=NDT(B5:B18,SEQUENCE(,12),1)
V5:V35V5=NDT(SEQUENCE(31,,1880),4,1)
I22:R35I22=NDT(B5:B18,12,SEQUENCE(,10))
Dynamic array formulas.
 
Neg sn dates.xlsx
ABCDEFGHIJKLMNOPQRSTUV
1If,y,m,d are 2D arrays
2YearsMonthsDays
3=RANDARRAY(10,6,1600,2022,1)=RANDARRAY(10,6,1,12,1)=RANDARRAY(10,6,1,31,1)
417481933191519461642189691288691132512616
517651858197218541724189712591127198428272
6198316511688176418031731101125412111792628
71992187919111761194416691244494301411302915
8182519521934197119081804427345191921261614
917202001176118231947194649121162151652121
10171517641829193118241660344334130197254
11196618011750189416291880371127972324301110
1218181729167618551755178969556524311714712
1317791856173619631820183241679124425251129
14
15=MAP(B4#,I4#,P4#,LAMBDA(y,m,d,NDT(y,m,d)))
16-5526212391571617026-94075-1201
17-48954-1521226546-16469-64225-912
1830600-90928-77087-49550-35333-61364
1933968-75664119-5064816344-84265
20-272841904312621260183029-34929
21-6563237139-50752-280881749216974
22-67510-49552-2582311389-27674-87563
2324173-35955-54458-2130-98788-7051
24-29775-62182-81676-16302-52802-40409
25-44080-16067-5972323217-28965-24808
26
27
28DATE vs NDT behavior, both are consistent with "off grid" dates like 30 Feb
29
30=DATE(2022,2,30)=B31
3102-03-2244622
32checking days "gap"
33=NDT(2022-400,2,30)=D31-B34
34-101476146098
35
NDT 1
Cell Formulas
RangeFormula
B3,D33,B33,D30,B30,B15,P3,I3B3=FORMULATEXT(B4)
B4:G13B4=RANDARRAY(10,6,1600,2022,1)
I4:N13I4=RANDARRAY(10,6,1,12,1)
P4:U13P4=RANDARRAY(10,6,1,31,1)
B16:G25B16=MAP(B4#,I4#,P4#,LAMBDA(y,m,d,NDT(y,m,d)))
B31B31=DATE(2022,2,30)
D31D31=B31
B34B34=NDT(2022-400,2,30)
D34D34=D31-B34
Dynamic array formulas.
 

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,637
Latest member
Ezio2866

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