In 21st century (i.e. 2000 – 2099), which years have the highest no. of workdays considering – 1. Sat and Sun as weekends 2. Holiday list given in A2:A5. These dates are fixed for every year, it means every year the holidays will fall on these dates only. Hence you need not consider year part in A2:A5 while I have inputted those as dates with 2022 year.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 59
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Workdays in Each Year with Power Query
Power Query solution 1 for Workdays in Each Year, proposed by Matthias Friedmann:
let
Source = Table.FromList({2000 .. 2099}, Splitter.SplitByNothing(), {"Year"}),
holidays = List.Buffer(Holidays),
#"Added Custom" = Table.AddColumn(
Source,
"Custom",
each List.Count(
List.Select(
{Number.From(hashtag#date([Year], 1, 1)) .. Number.From(hashtag#date([Year], 12, 31))},
each
let
date = Date.From(_, "de-DE")
in
Date.DayOfWeek(date, Day.Monday) < 5 and
not List.Contains(holidays, Text.Start(Text.From(date, "de-DE"), 6))
)
)
),
Max = List.Max(#"Added Custom"[Custom]),
#"Filtered Rows" = Table.SelectRows(
#"Added Custom",
each [Custom] = Max
)[[Year]]
in
#"Filtered Rows"
Power Query solution 2 for Workdays in Each Year, proposed by Matthias Friedmann:
let
Source = Table.FromList({2000 .. 2099}, Splitter.SplitByNothing(), {"Year"}),
holidays = List.Buffer(Holidays),
#"Added Custom" = Table.AddColumn(
Source,
"Custom",
each List.Count(
List.Select(
List.Dates(hashtag#date([Year],1,1), Number.From(hashtag#date([Year],12,31) - hashtag#date([Year],1,1)) + 1,hashtag#duration(1,0,0,0)),
each
Date.DayOfWeek(_, Day.Monday) < 5 and
not List.Contains(holidays, Text.Start(Text.From(_, "de-DE"), 6))
)
)
),
Max = List.Max(#"Added Custom"[Custom]),
#"Filtered Rows" = Table.SelectRows(
#"Added Custom",
each [Custom] = Max
)[[Year]]
in
#"Filtered Rows"
Power Query solution 3 for Workdays in Each Year, proposed by Excel BI:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
HolidaysList = List.Transform(Source[Holidays], each "-"&Text.Middle(Text.From(_),3,2)&"-"&Text.Start(Text.From(_),2)),
YearsTbl = Table.FromColumns({{2000..2099}}, type table [Year=Int64.Type]),
#"Added Custom" = Table.AddColumn(YearsTbl, "Days", each [d=List.Dates(hashtag#date([Year],1,1),Duration.Days(hashtag#date([Year],12,31)-hashtag#date([Year],1,1))+1,hashtag#duration(1,0,0,0)),
h=List.Transform(HolidaysList, (x)=>Date.From(Text.From([Year])&x)),
dhDiff=List.Difference(d,h),
r=List.Sum(List.Transform(dhDiff, each Number.From(Date.DayOfWeek(_,1)<5)))][r], Int64.Type),
MaxDays = List.Max(#"Added Custom"[Days]),
Result = Table.RemoveColumns(Table.SelectRows(#"Added Custom", each ([Days] = MaxDays)),{"Days"})
in
Result
Power Query solution 4 for Workdays in Each Year, proposed by Melissa de Korte:
let
Source = List.Buffer( Excel.CurrentWorkbook(){[Name="Table1"]}[Content][Holidays] ),
WorkingDays = List.Buffer(
List.Transform(
{2000..2099},
(x)=>
List.Count( List.Select( { Number.From( hashtag#date(x, 1, 1))..Number.From(hashtag#date(x, 12, 31)) }, each Number.Mod(_,7)>1)) -
List.Count( List.Select( List.Transform( Source, each Date.DayOfWeek( hashtag#date( x, Date.Month(_), Date.Day(_)), Day.Monday) < 5), each _ = true))
)
),
Max = List.Max( WorkingDays ),
Result = Table.SelectRows(
Table.FromColumns(
{
{2000..2099},
WorkingDays
}, {"Year", "Workingdays"}
), each [Workingdays] = Max
)[[Year]]
in
Result
Power Query solution 5 for Workdays in Each Year, proposed by Melissa de Korte:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
WorkingDays = List.Buffer( List.Transform(
List.Split(
List.TransformMany(
{2000..2099},
each Source[Holidays],
(x, y)=> [
HolidayIsWeekday = Date.DayOfWeek( hashtag#date( x, Date.Month(y), Date.Day(y)), Day.Monday) < 5,
DaysInYear = List.Count( List.Select( { Number.From( hashtag#date(x, 1, 1))..Number.From(hashtag#date(x, 12, 31)) }, each Number.Mod(_,7)>1))
]
), Table.RowCount( Source )
), each _{0}[DaysInYear] - List.Count( List.Select(_, each _[HolidayIsWeekday] = true))
)),
Max = List.Max( WorkingDays ),
Result = Table.SelectRows(
Table.FromColumns(
{
{2000..2099},
WorkingDays
}, {"Year", "Workingdays"}
), each [Workingdays] = Max
)[[Year]]
in
Result
Power Query solution 6 for Workdays in Each Year, proposed by Abdoul Karim N.:
let
Source = Excel.CurrentWorkbook(){[Name = "Holidays"]}[Content],
GetID = [
ChangedType = Table.TransformColumnTypes(Source, {{"Holidays", type date}}),
GetMonth = Table.AddColumn(ChangedType, "Month", each Date.Month([Holidays]), type text),
GetDay = Table.AddColumn(GetMonth, "Day", each Date.Day([Holidays]), type text),
ID = Table.AddColumn(GetDay, "ID Date", each Text.From([Day]) & Text.From([Month]), Int64.Type),
HolidayTable = Table.SelectColumns(ID, {"ID Date"})
][HolidayTable][ID Date]
in
GetIDPower Query solution 7 for Workdays in Each Year, proposed by Dominic Walsh:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Start = Table.TransformColumns(Source, {{"Holidays", each Text.Start(Text.From(_, "en-GB"), 5), type text}}),
Range = Table.FromList({Number.From(hashtag#date(2000, 01, 01))..Number.From(hashtag#date(2099, 12, 31))}, Splitter.SplitByNothing()),
Date = Table.TransformColumnTypes(Range,{{"Column1", type date}}),
Day = Table.AddColumn(Date, "Day Name", each Date.DayOfWeekName([Column1]), type text),
Work = Table.SelectRows(Day, each not Text.StartsWith([Day Name], "S")),
Dup = Table.DuplicateColumn(Work, "Column1", "Copy"),
Start1 = Table.TransformColumns(Dup, {{"Copy", each Text.Start(Text.From(_, "en-GB"), 5), type text}}),
Join = Table.NestedJoin(Start1,"Copy",Start,"Holidays","Table"),
Expand = Table.ExpandTableColumn(Join, "Table", {"Holidays"}, {"Holidays"}),
Filter = Table.SelectRows(Expand, each [Holidays] = null),
Year = Table.AddColumn(Filter, "Year", each Date.Year([Column1]), Int64.Type),
Remove = Table.SelectColumns(Year,{"Column1", "Year"}),
Group = Table.Group(Remove, {"Year"}, {{"Sum", each Table.RowCount(_), Int64.Type}}),
Max = List.Max(Group[Sum]),
Result = Table.SelectRows(Group, each [Sum] = Max)
in
Result
Solving the challenge of Workdays in Each Year with Excel
Excel solution 1 for Workdays in Each Year, proposed by Rick Rothstein:
=LET(a,
A2:A5,
s,
ROW(
2000:2099
),
n,
NETWORKDAYS(
DATE(
s,
1,
1
),
DATE(
s,
12,
31
)
)-MAP(s,
LAMBDA(x,
SUM(0+(WEEKDAY(
DATE(
x,
MONTH(
a
),
DAY(
a
)
),
2
)<6)))),
FILTER(
s,
n=MAX(
n
)
))
Note: Edited from my original post in order to save 7 characters (changed a SEQUENCE expression to a ROW expression)Excel solution 2 for Workdays in Each Year, proposed by John V.:
=LET(x,ROW(2000:2099),d,NETWORKDAYS(x&"-1-1",x&"-12-31",x&TOROW(TEXT(A2:A5,"-m-d"))),FILTER(x,d=MAX(d)))Excel solution 3 for Workdays in Each Year, proposed by محمد حلمي:
=LET(
C,
A2:A5,
B,
ROW(
2000:2099
),
A,
SCAN(
0,
B,
LAMBDA(
Z,
X,
NETWORKDAYS.INTL(
DATE(
X,
1,
1
),
EDATE(
DATE(
X,
1,
1
),
12
)-1,
,
DATE(
X,
MONTH(
C
),
DAY(
C
)
)
)
)
),
FILTER(
B,
A=MAX(
A
)
)
)Excel solution 4 for Workdays in Each Year, proposed by محمد حلمي:
TRANSPOSE(
A2:A5
)Excel solution 5 for Workdays in Each Year, proposed by Julian Poeltl:
=LET(H,A2:A5,S,SEQUENCE(100,,2000),D,DATE(S,1,1),NW,NETWORKDAYS(D,EOMONTH(D,11)),F,MAP(S,LAMBDA(A,SUM(IF(WEEKDAY(DATE(A,MONTH(H),DAY(H)),2)>5,0,1)))),FILTER(S,NW-F=MAX(NW-F)))Excel solution 6 for Workdays in Each Year, proposed by Aditya Kumar Darak 🇮🇳:
= LET(
_s,
2000,
_e,
2099,
_h,
TRANSPOSE(
A2:A5
),
_y,
SEQUENCE(
_e - _s + 1,
,
_s
),
_calc,
NETWORKDAYS(
DATE(
_y,
1,
1
),
DATE(
_y,
12,
31
),
DATE(
_y,
MONTH(
_h
),
DAY(
_h
)
)
),
_final,
FILTER(
_y,
_calc = MAX(
_calc
)
),
_final
)Excel solution 7 for Workdays in Each Year, proposed by Timothée BLIOT:
=LET(Start,
2000,
Period,
100,
Years,
SEQUENCE(
Period,
,
Start
),
Holidays,
DATE(
Start,
MONTH(
A2:A5
),
DAY(
A2:A5
)
),
NumberDays,
DATE(
Years,
12,
31
)-DATE(
Years,
1,
1
),
TextDates,
BYROW(
SEQUENCE(
Period
),
LAMBDA(
a,
TEXTJOIN(
".",
1,
DATE(
INDEX(
Years,
a
),
1,
0
)+SEQUENCE(
MAX(
INDEX(
NumberDays,
a
)
)+1
)
)
)
),
Dates,
TRANSPOSE(
VALUE(
MAKEARRAY(
ROWS(
TextDates
),
366,
LAMBDA(
a,
b,
INDEX(
TEXTSPLIT(
INDEX(
TextDates,
a,
1
),
".",
,
1
),
b
)
)
)
)
),
DatesHolidays,
--NOT(BYROW(INDEX(
Dates,
,
1
),
LAMBDA(a,
SUMPRODUCT(1*(a=TRANSPOSE(
Holidays
)))))),
WithoutHolidays,
FILTER(
Dates,
DatesHolidays,
""
),
WorkingDays,
MAP(
SEQUENCE(
,
COLUMNS(
WithoutHolidays
)
),
LAMBDA(
b,
SUM(
IF(
IFERROR(
WEEKDAY(
INDEX(
WithoutHolidays,
,
b
),
2
),
8
)>=6,
0,
1
)
)
)
),
FILTER(
Years,
TRANSPOSE(
WorkingDays
) = MAX(
TRANSPOSE(
WorkingDays
)
),
""
))Excel solution 8 for Workdays in Each Year, proposed by Charles Roldan:
=LET(
Holidays,
TOROW(
A2:A5
),
Years,
SEQUENCE(
100,
,
2000
),
Work,
NETWORKDAYS(
DATE(
Years,
1,
1
),
DATE(
Years,
12,
31
),
DATE(
Years,
MONTH(
Holidays
),
DAY(
Holidays
)
)
),
FILTER(
Years,
Work = MAX(
Work
)
)
)Excel solution 9 for Workdays in Each Year, proposed by Victor Momoh (MVP, MOS, R.Eng):
=LET(a,MAP(ROW(2000:2099),LAMBDA(x,NETWORKDAYS(DATE(x,1,1),DATE(x+1,1,0),DATE(x,MONTH(A$2:A$5),DAY($A$2:$A$5))))),FILTER(ROW(2000:2099),a=MAX(a)))Excel solution 10 for Workdays in Each Year, proposed by Sergei Baklan:
=VSTACK(
"Answer",
LET(
years, SEQUENCE(100,, 2000),
wd, SCAN(0, years,
LAMBDA(a,v, NETWORKDAYS(DATE(v,1,1),DATE(v,12,31), DATE(v, MONTH(holidays), DAY(holidays) ) ) ) ),
m, MAX(wd),
FILTER(years, wd=m) ) )Excel solution 11 for Workdays in Each Year, proposed by RIJESH T.:
=LET(h,A2:A5,y,SEQUENCE(99,,2000),d,MAP(y,LAMBDA(a,NETWORKDAYS(DATE(a,1,1),DATE(a,12,31),DATE(a,MONTH(h),DAY(h))))),FILTER(y,d=MAX(d)))Excel solution 12 for Workdays in Each Year, proposed by Viswanathan M B:
=Let(
Yrs,
sequence(
100,
1,
2000
),
Days,
MAP(
Yrs,
LAMBDA(
a,
NETWORKDAYS.INTL(
DATE(
a,
1,
1
),
DATE(
a,
12,
31
),
1,
DATE(
a,
MONTH(
A2:A5
),
& DAY(
A2:A5
)
)
)
)
),
Filter(
Yrs,
Days=Max(
Days
)
)
)Excel solution 13 for Workdays in Each Year, proposed by Stevenson Yu:
=LET(
A,
ROW(
2000:2099
),
B,
TOROW(
A2:A5
),
F,
NETWORKDAYS(
DATE(
A,
1,
1
),
DATE(
A,
12,
31
),
DATE(
A,
MONTH(
B
),
DAY(
B
)
)
),
FILTER(
A,
F=MAX(
F
)
)
)Excel solution 14 for Workdays in Each Year, proposed by Tukaram Jogdand:
= CALENDAR(
DATE(
2000,
1,
1
),
DATE(
2099,
1,
1
)
)
2. Check day = IF(
OR(
WEEKDAY(
'Date Table'[Date],
1
)=1,
WEEKDAY(
'Date Table'[Date],
1
)=7
),
"Holiday",
IF(
RELATED(
holidays[DateMonthKey]
)<>BLANK(),
"Holiday",
"Workday"
)
)
3. Weekday = WEEKDAY(
'Date Table'[Date],
1
)
4. Working Days = COUNTROWS(
FILTER(
'Date Table',
'Date Table'[Check day]<>"Holiday"
)
)&&