Find Last Mondays after 25th

Provide a formula to list the Last Mondays of each month for a given year in A1 if Last Monday falls on a day > 25. For year 2022, 25-Apr-22 and 25-Jul-22 are also last Mondays but they are not greater than 25, hence they are excluded.

📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 52
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn

Solving the challenge of Find Last Mondays after 25th with Power Query

Power Query solution 1 for Find Last Mondays after 25th, proposed by Aditya Kumar Darak 🇮🇳:

let
 Year   = 2022,
 MyFun  = ( Number as number ) =>
 Date.StartOfWeek ( Date.EndOfMonth ( hashtag#date ( Year, Number, 1 ) ) ),
 Generate = List.Generate (
 () => [ x = 1, y = MyFun ( x ) ],
 each [x] <= 12,
 each [ x = [x] + 1, y = MyFun ( x ) ],
 each [y]
 ),
 Return  = List.Select ( Generate, each Date.Day ( _ ) > 25 )
in
 Return

Power Query solution 2 for Find Last Mondays after 25th, proposed by Luan Rodrigues:

let
 Fonte = [
 l =List.Select(
 List.Transform(
 {Number.From(Date.From(hashtag#date(2022, 1, 1)))..Number.From(Date.From(hashtag#date(2022, 12, 31)))},each Date.From(_)), 
 each Date.Day(_) > 25)
 ][l], 
 tab = Table.FromList(Fonte, Splitter.SplitByNothing(), null, null, ExtraValues.Error), 
 Segunda = Table.RenameColumns(
 Table.SelectRows(
 Table.AddColumn(tab, "Dia", each Date.DayOfWeek([Column1])), 
 each [Dia] = 1),
 {{"Column1", "2022"}}
 )[[2022]],
 Tipo = Table.TransformColumnTypes(Segunda, {{"2022", type text}}, "en-US")
in
 Tipo

Power Query solution 3 for Find Last Mondays after 25th, proposed by Matthias Friedmann:

let
 Source = {Number.From(hashtag#date(2022, 1, 1)) .. Number.From(hashtag#date(2022, 12, 31))}, 
 #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), {"Dates"}), 
 #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table", {{"Dates", type date}}), 
 #"Filtered Rows" = Table.SelectRows(
 #"Changed Type", 
 each Date.Day([Dates]) > 25 and Date.DayOfWeekName([Dates]) = "Monday"
 )
in
 #"Filtered Rows"

Note: You can replace 2022 with a reference or parameter:
{Number.From(hashtag#date(Year,1,1))..Number.From(hashtag#date(Year,12,31))}

Power Query solution 4 for Find Last Mondays after 25th, proposed by Rafael González B.:

let
Origen = Table.FromList(List.Dates(hashtag#date(2022,1,1), 365, hashtag#duration(1,0,0,0)), Splitter.SplitByNothing(), {"2022"} , null, ExtraValues.Error),
 FilteredRows= Table.SelectRows(Origen, each ((if (Date.DayOfWeekName([2022])= "lunes") and (Date.Day([2022]) > 25) then "SI" else "NO") = "SI"))

in
 FilteredRows

😁

Power Query solution 5 for Find Last Mondays after 25th, proposed by Venkata Rajesh:

let
 Output = List.Select(List.Transform({1..12}, each let 
 _EndOfMonth = Date.EndOfMonth(hashtag#date(2022,_,1))
 in Date.AddDays(_EndOfMonth, -1 * Date.DayOfWeek(_EndOfMonth, Day.Monday))), 
 each Date.Day(_) > 25 )
 in 
 Output

Power Query solution 6 for Find Last Mondays after 25th, proposed by Abdoul Karim N.:

let
 StartDate= hashtag#date(2022,01,01),
 EndDate= hashtag#date(2022,12,31),
 Source =List.Dates(StartDate, 365,hashtag#duration(1,0,0,0) ),
 GetTableFromList = Table.FromList(Source,Splitter.SplitByNothing() ,{"Dates"}),
 DayofWeek = Table.AddColumn(GetTableFromList, "DayofWeek", each Date.DayOfWeekName([Dates])),
 DayNumber = Table.AddColumn(DayofWeek, "Day", each Date.Day([Dates]), Int64.Type),
 Filter = Table.SelectRows(DayNumber, each ([Day] > 25 and [DayofWeek]="lunedì")),
 ChangedType = Table.TransformColumnTypes(Filter,{{"Dates", type date}})
in
ChangedType

Solving the challenge of Find Last Mondays after 25th with Excel

Excel solution 1 for Find Last Mondays after 25th, proposed by Rick Rothstein:

=LET(r,
    ROW(
        1:12
    ),
    d,
    DATE(
        A1,
        r,
        6
    ),
    f,
    d+23-WEEKDAY(
        d
    ),
    m,
    f+7*(MONTH(
        f
    )=MONTH(
        f+7
    )),
    FILTER(
        m,
        DAY(
            m
        )>25
    ))

Excel solution 2 for Find Last Mondays after 25th, proposed by Rick Rothstein:

=LET(
    m,
    MAP(
        ROW(
            1:12
        ),
        LAMBDA(
            x,
            LET(
                e,
                EOMONTH(
                    DATE(
                        A1,
                        x,
                        1
                    ),
                    0
                ),
                e+2-WEEKDAY(
                    e
                )
            )
        )
    ),
    FILTER(
        m,
        DAY(
            m
        )>25
    )
)

Excel solution 3 for Find Last Mondays after 25th, proposed by John V.:

=LET(
    b,
    DATE(
        A1,
        ROW(
            2:13
        ),
        
    ),
    m,
    b-WEEKDAY(
        b,
        3
    ),
    FILTER(
        m,
        DAY(
            m
        )>25
    )
)

Excel solution 4 for Find Last Mondays after 25th, proposed by John V.:

=LET(b,DATE(A1,1,ROW(1:366)),FILTER(b,(WEEKDAY(b)=2)*(DAY(b)>25)))

Excel solution 5 for Find Last Mondays after 25th, proposed by محمد حلمي:

=LET(
    i,
    ROW(
        1:12
    ),
    d,
    DATE(
        A1,
        i,
        29
    )-WEEKDAY(
        DATE(
            YEAR(
                A1
            ),
            i,
            5
        )
    ),
    e,
    EOMONTH(
        DATE(
            A1,
            i,
            5
        ),
        0
    ),
    o,
    IF(
        e-d>6,
        d+7,
        d
    ),
    FILTER(
        o,
        DAY(
            o
        )>25
    )
)

Excel solution 6 for Find Last Mondays after 25th, proposed by محمد حلمي:

=LET(a,
    SEQUENCE(
        366,
        ,
        DATE(
            A1,
            1,
            1
        )
    ),
    FILTER(a,
    (TEXT(
        a,
        "ddd"
    )="mon")*(DAY(
        a
    )>25)))

Excel solution 7 for Find Last Mondays after 25th, proposed by 🇰🇷 Taeyong Shin:

=LET(m,WORKDAY.INTL(2022&-SEQUENCE(12)&-24,1,"0111111"),FILTER(m,DAY(m)>25))

Excel solution 8 for Find Last Mondays after 25th, proposed by Kris Jaganah:

=LET(
    a,
    FILTER(
        DATE(
            2022,
            1,
            SEQUENCE(
                365
            )
        ),
        WEEKDAY(
            DATE(
            2022,
            1,
            SEQUENCE(
                365
            )
        ),
            2
        )=1
    ),
    FILTER(
        a,
        DAY(
            a
        )>25
    )
)

Excel solution 9 for Find Last Mondays after 25th, proposed by Julian Poeltl:

=LET(S,EOMONTH(DATE(A1,SEQUENCE(12),1),0),W,WEEKDAY(S,2)-1,D,S-W,R,IF(DAY(D)<26,"",D),FILTER(R,R<>""))

Excel solution 10 for Find Last Mondays after 25th, proposed by Aditya Kumar Darak 🇮🇳:

= LET(
    
     _y,
    
     2022,
    
     _d,
    
     DATE(
         _y,
          SEQUENCE(
              12
          ) + 1,
          1
     ),
    
     _lm,
    
     _d - WEEKDAY(
         _d - 2,
          1
     ),
    
     FILTER(
         _lm,
          DAY(
              _lm
          ) > 25
     )
)

Excel solution 11 for Find Last Mondays after 25th, proposed by Timothée BLIOT:

=LET(
    
    Months,
     EOMONTH(
         DATE(
             A1,
             1,
             1
         ),
         SEQUENCE(
             12,
             ,
             0
         )
     ),
    
    Days,
     WEEKDAY(
         Months,
         3
     ),
    
    LastMonday,
     Months-Days,
    
    FILTER(
        LastMonday,
        DAY(
            LastMonday
        )>25
    )
)

Excel solution 12 for Find Last Mondays after 25th, proposed by Bhavya Gupta:

=LET(Y,A1,
 LD,EOMONTH(--DATE(Y,SEQUENCE(12),1),0),
 WD,LD-WEEKDAY(LD,3),
 FILTER(WD,DAY(WD)>25))

=LET(Y,A1,
 LD,EDATE(DATE(A1,1,1),SEQUENCE(12)),
 WD,LD-WEEKDAY(LD,3),
 FILTER(WD,DAY(WD)>25))

=LET(Mn,DATE(A1,1,1),
 S,SEQUENCE(DATE(A1,12,31)-Mn+1,,Mn),
 La,IF(WEEKDAY(S)=2,MONTH(S)),
 Ra,XLOOKUP(SEQUENCE(12),La,S,,,-1),
 FILTER(Ra,DAY(Ra)>25))

Excel solution 13 for Find Last Mondays after 25th, proposed by Stefan Olsson:

=QUERY({ArrayFormula(LAMBDA(
    _d,
    {_d,
    DAY(
        _d
    ),
    WEEKDAY(
        _d,
        2
    ),
    YEAR(
        _d
    )}
)(SEQUENCE(
    366,
    1,
    DATE(
        A1,
        1,
        1
    ),
    1
)))},
    "select Col1 where Col3=1 and Col2>25 and Col4="&A1)

Original post which works most of the time (but not for 2012,
     2040,
     2068,
    ...)
Maybe a little bit long,
     but this does the job in Google Sheets:
=QUERY({ArrayFormula(LAMBDA(
    _d,
    {_d,
    DAY(
        _d
    ),
    WEEKDAY(
        _d,
        2
    )}
)(SEQUENCE(
    365,
    1,
    DATE(
        A1,
        1,
        1
    ),
    1
)))},
    "select Col1 where Col3=1 and Col2>25")

Excel solution 14 for Find Last Mondays after 25th, proposed by Jardiel Euflázio:

=LET(a,SEQUENCE(1+("12/31/"&A1)-("01/01/"&A1),,("01/01/"&A1)),FILTER(a,(WEEKDAY(a)=2)*(DAY(a)>25)))

Excel solution 15 for Find Last Mondays after 25th, proposed by Jardiel Euflázio:

=LET(a,
    ROW(
        INDIRECT(
            DATE(
                A1,
                1,
                1
            )&":"&DATE(
                A1,
                12,
                31
            )
        )
    ),
    FILTER(a,
    (WEEKDAY(
        a
    )=2)*(DAY(
        a
    )>25)))

=LET(a,
    SEQUENCE(
        1+DATE(
                A1,
                12,
                31
            )-DATE(
                A1,
                1,
                1
            ),
        ,
        DATE(
                A1,
                1,
                1
            )
    ),
    FILTER(a,
    (WEEKDAY(
        a
    )=2)*(DAY(
        a
    )>25)))

Excel solution 16 for Find Last Mondays after 25th, proposed by Victor Momoh (MVP, MOS, R.Eng):

=LET(a,WORKDAY.INTL(DATE(A1,1+SEQUENCE(12),1),-1,"0111111"),FILTER(a,DAY(a)>25))

Excel solution 17 for Find Last Mondays after 25th, proposed by RIJESH T.:

=LET(
    d,
    DATE(
        2022,
        SEQUENCE(
            12
        ),
        25
    ),
    w,
    WORKDAY.INTL(
        --d,
        1,
        "0111111"
    ),
    FILTER(
        w,
        DAY(
            w
        )>25
    )
)

Excel solution 18 for Find Last Mondays after 25th, proposed by Sarun Chimamphant:

=LET(
    a,
    EOMONTH(
        A1&"-1-1",
        SEQUENCE(
            12,
            ,
            0
        )
    ),
    b,
    MOD(
        WEEKDAY(
            a,
            12
        ),
        7
    ),
    FILTER(
        a-b,
        DAY(
            a-b
        )>25
    )
)

Excel solution 19 for Find Last Mondays after 25th, proposed by Riley Johnson:

=LET(
 _month_ends, EOMONTH( DATE( 2022, SEQUENCE( 12 ), 1 ), 0 ),
 _day_of_week, WEEKDAY( _month_ends, 3 ),
 _prev_monday, _month_ends - _day_of_week,
 FILTER( _prev_monday, DAY( _prev_monday ) > 25, "No Monday After The 25th" )
)

Excel solution 20 for Find Last Mondays after 25th, proposed by ASHFAQUE AHMED:

=EOMONTH(DATE(2021,ROW(1:12),1),0)-WEEKDAY(EOMONTH(DATE(2021,ROW(1:12),1),0)-0,3)

Excel solution 21 for Find Last Mondays after 25th, proposed by Kamaalpreet Sudan PMO-CP®, PgMP®, PMP®, PMI-ACP®:

=TEXT(
    LET(
        b,
        LET(
            a,
            DATE(
                A1,
                SEQUENCE(
                    12,
                    1,
                    2,
                    1
                ),
                
            ),
            a-WEEKDAY(
                a,
                3
            )
        ),
        FILTER(
            b,
            DAY(
                b
            )>25
        )
    ),
    "mm/dd/yyyy"
)

Solving the challenge of Find Last Mondays after 25th with DAX

DAX solution 1 for Find Last Mondays after 25th, proposed by Zoran Milokanović:

DEFINE
VAR Y = SELECTCOLUMNS(Input, Input[Year])
VAR C = CALENDAR(DATE(Y, 1, 1), DATE(Y, 12, 31))
EVALUATE
SELECTCOLUMNS(
 FILTER(
 GROUPBY(
 ADDCOLUMNS(FILTER(C, WEEKDAY([Date]) = 2), "M", MONTH([Date])), [M], "D", MAXX(CURRENTGROUP(), [Date])),
 DAY([D]) > 25
 ),
"Date", [D]
)
 
                 &

Solving the challenge of Find Last Mondays after 25th with Power Query

Solving the challenge of Find Last Mondays after 25th with Excel

Solving the challenge of Find Last Mondays after 25th with DAX

Leave a Reply Cancel reply