This challenge is contributed by 🇰🇷 Taeyong Shin Create a table that calculates the number of weekdays, Saturdays, and Sundays using yearly and monthly data, and displays the total number of days in each month.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 595
Challenge Difficulty: ⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Calculate Monthly Day Totals with Power Query
Power Query solution 1 for Calculate Monthly Day Totals, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
B = {"Weekdays","Saturdays" ,"Sundays","" },
C = Table.AddColumn(A, "Days", each let
a = hashtag#date([Year],[Month],1),
b = Date.EndOfMonth(a),
c = List.Dates(a, Number.From(b-a)+1 ,hashtag#duration(1,0,0,0)),
d = (x,y)=> List.Sum( List.Transform( c , each Number.From( Date.DayOfWeek (_ ,x) < y ))),
e = List.Transform( {{1,5} ,{6,1},{0,1},{0,7}}, each d(_ {0}, _{1} )),
f = Table.FromRows({e} ,B ) in f),
D = Table.ExpandTableColumn(C, "Days", B),
E = Table.UnpivotOtherColumns(D, {"Year", "Month"}, "Type", "Days"),
F = Table.TransformRows( E , (x)=> if x[Type] = "" then
Record.TransformFields (x, {{"Year" ,each "Total:"},{"Month" ,each "" }}) else x),
G = Table.FromRecords(F)
in G
Power Query solution 2 for Calculate Monthly Day Totals, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[ Name = "data" ]}[Content],
Types = { "Sunday", "Saturday", "Weekday" },
ToRecords = Table.ToRecords ( Source ),
Generate = List.TransformMany (
ToRecords,
each [
Ds = hashtag#date ( [Year], [Month], 1 ),
Td = Date.DaysInMonth ( Ds ),
Dt = List.Dates ( Ds, Td, Duration.From ( 1 ) ),
TR = [ Year = "Total", Month = null, Days = Td ],
T = List.Transform (
Dt,
( f ) => [ dow = Date.DayOfWeek ( f, 6 ), mx = List.Min ( { dow, 2 } ), r = Types{mx} ][r]
),
Ty = List.Transform (
Types,
( f ) =>
Record.AddField ( [ Types = f ], "Days", List.Count ( List.PositionOf ( T, f, 2 ) ) )
),
R = List.Reverse ( Ty ) & { TR }
][R],
( x, y ) => x & y
),
Return = Table.FromRecords ( Generate, null, 2 )
in
Return
Power Query solution 3 for Calculate Monthly Day Totals, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Sol = Table.Combine(Table.Group(Source, {"Year", "Month"}, {{"A", each
let
a = _,
b = Table.ToRows(a){0},
c = hashtag#date(b{0},b{1},1),
d = Date.EndOfMonth(hashtag#date(b{0},b{1},1)),
e = List.Transform({Number.From(c)..Number.From(d)},
each Date.DayOfWeek(Date.From(_),1)),
f = List.Transform({"<", "=", ">"},
(x)=> List.Count(List.Select(e, each Expression.Evaluate(Text.From(_)&x&"5")))),
g = {"Weekdays", "Saturdays", "Sundays"},
h = List.Zip({{b{0}}, {b{1}}, g,f})&{{"Total:", "", "", List.Sum(f)}},
i = Table.FromRows(h, Table.ColumnNames(a)&{"Type","Days"}),
j = Table.FillDown(i, Table.ColumnNames(a))
in j}})[A])
in
Sol
Power Query solution 4 for Calculate Monthly Day Totals, proposed by Abdallah Ally:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Transform = Table.TransformRows(
Source,
each [
a = [Year],
b = [Month],
c = hashtag#date(a, b, 1),
d = Date.EndOfMonth(c),
e = List.Dates(c, Duration.Days(d - c) + 1, Duration.From(1)),
f = List.Count(e),
g = List.Count(List.Select(e, each Date.DayOfWeek(_, Day.Saturday) > 1)),
h = List.Count(List.Select(e, each Date.DayOfWeek(_, Day.Saturday) = 0)),
i = List.Count(List.Select(e, each Date.DayOfWeek(_, Day.Saturday) = 1)),
j = {
{a, b, "Weekdays", g},
{a, b, "Saturdays", h},
{a, b, "Sundays", i},
{"Total:", null, null, f}
}
][j]
),
Result = Table.FromRows(List.Combine(Transform), Table.ColumnNames(Source) & {"Type", "Days"})
in
Result
Power Query solution 5 for Calculate Monthly Day Totals, proposed by 🇵🇪 Ned Navarrete C.:
let
Origen = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
AddColumn = Table.AddColumn(Origen, "X", each [y = [Year],m=[Month],f = hashtag#date(y,m,1),nd = Date.Day(Date.EndOfMonth(f)),lf = List.Dates(f,nd,hashtag#duration(1,0,0,0)),ld = (x)=> List.Transform(lf,each Number.From(Date.DayOfWeek(_)=x)),satd = List.Sum(ld(5)),sund = List.Sum(ld(6)),wd = nd-satd-sund,t = hashtag#table({"Year","Month","Type","Days"},{{y,m,"Weekdays",wd},{y,m,"Saturdays",satd},{y,m,"Sundays",sund},{"Total:",null,null,nd}})][t]),
Result =Table.Combine(AddColumn[X])
in
Result
Power Query solution 6 for Calculate Monthly Day Totals, proposed by Alexandre Garcia:
let
A = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
B = {"Weekdays", "Saturdays", "Sundays"},
C = {"Type","Days"},
D = (x,y)=> [a = hashtag#date(x{0},x{1},1), b = List.Count(List.RemoveItems(List.Transform(List.Dates(a, Duration.Days(Date.EndOfMonth(a) - a) +1, Duration.From(1)), each Date.DayOfWeek(_,6)), y))] [b],
E = List.TransformMany(Table.ToRows(A), each {List.Zip({B} & {{D(_,{0..1}),D(_,{1..6}), D(_,{2..6} & {0})}})}, (x,y)=> Table.InsertRows(Table.FromRows({x & {Table.FromRows(y, C)}}, {"Year", "Month"} & {"x"}), 1, {[Year = "Total:", Month = null, x = Table.FromRows ({{D(x, {null})}}, {"Days"})]})),
F = Table.ExpandTableColumn(Table.Combine(E), "x", C)
in F
Power Query solution 7 for Calculate Monthly Day Totals, proposed by Mihai Radu O:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
s = Table.Combine(Table.AddColumn(Source, "r", each [
a = hashtag#date([Year],[Month],1),
b = Date.EndOfMonth(a),
c = List.Dates(a,Duration.Days(b-a)+1,hashtag#duration(1,0,0,0)),
d = List.Sort(c, (x,y)=>Value.Compare(Date.DayOfWeek(x,Day.Monday), Date.DayOfWeek(y,Day.Monday))),
e = List.Transform(d,(x)=> [e1 = Date.DayOfWeek(x,Day.Monday), e2 = if e1<5 then "Weekday" else if e1 = 5 then "Saturday" else "Sunday"][e2]),
f = List.Transform(List.Distinct(e), (x)=> List.Count(List.Select(e,each _ = x))),
yr = List.Repeat({[Year]},3), mt = List.Repeat({[Month]},3),
g = Table.FromColumns({yr,mt,List.Distinct(e),f}),
h = List.Transform({0..List.Count(Table.ToColumns(g))-1}, (x)=> if x = 0 then "Total" else if x= 3 then List.Sum(f) else ""),
i = Table.FromRows(Table.ToRows(g)&{h}, {"Year", "Month", "Type", "Days"})
][i])[r])
in
s
Power Query solution 8 for Calculate Monthly Day Totals, proposed by Krzysztof Kominiak:
let
Source = TableData,
AddLDates = Table.AddColumn(Source, "NT", each List.Transform({ Number.From(hashtag#date([Year],[Month],1))..Number.From(Date.EndOfMonth(hashtag#date([Year],[Month],1))) }, Date.From) ),
AddWeekdays = Table.AddColumn(AddLDates, "Weekdays", each List.Count(List.Select(List.Accumulate( [NT], {} , (s,c) => s & {Date.DayOfWeek(c,Day.Monday)+1} ), each _<6)) ),
AddSaturdays = Table.AddColumn(AddWeekdays, "Saturday", each List.Count(List.Select(List.Accumulate( [NT], {} , (s,c) => s & {Date.DayOfWeek(c,Day.Monday)+1} ), each _=6))),
AddSundays = Table.AddColumn(AddSaturdays, "Sundays", each List.Count(List.Select(List.Accumulate( [NT],{},(s,c) => s & {Date.DayOfWeek(c,Day.Monday)+1} ), each _>6))),
RemCols = Table.RemoveColumns(AddSundays,{"NT"}),
UnpivotOthCols = Table.UnpivotOtherColumns(RemCols, {"Year", "Month"}, "Days", "Value"),
Result = Table.Combine( Table.Group( UnpivotOthCols, {"Year", "Month"}, {{"NT", each Table.FromRows( Table.ToRows( _ ) & {{"Total:","","", List.Sum([Value])}}, {"Year","Month","Type","Days"})}} ) [NT] )
in
Result
Power Query solution 9 for Calculate Monthly Day Totals, proposed by Francesco Bianchi 🇮🇹:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
d = Table.AddColumn(Source, "Custom", each
let
d = hashtag#date([Year],[Month],1),
l = List.Dates( d, Number.From(Date.EndOfMonth(d)-d)+1, hashtag#duration(1,0,0,0)),
t = List.Transform(l, each Date.DayOfWeek(_)),
r = [Weekdays = List.Count(List.Select (t, each _< 5)), Saturdays = List.Count(List.Select (t, each _ = 5)), Sundays = List.Count(List.Select (t, each _ = 6)), #"Total:"= List.Count(t) ]
in r),
e = Table.ExpandRecordColumn(d, "Custom", {"Weekdays", "Saturdays", "Sundays", "Total:"}, {"Weekdays", "Saturdays", "Sundays", "Total:"}),
u = Table.UnpivotOtherColumns(e, {"Year", "Month"}, "Type", "Days"),
s = Table.FromRows( List.Transform( Table.ToRows( u), each if _{2}="Total:" then {"Total:","","",_{3}} else _), Table.ColumnNames(u))
in
s
Solving the challenge of Calculate Monthly Day Totals with Excel
Excel solution 1 for Calculate Monthly Day Totals, proposed by Bo Rydobon 🇹🇭:
=REDUCE(
D1:G1,
B2:B4,
LAMBDA(
a,
m,
LET(
y,
@+A4:m,
s,
y&-m&-1,
e,
EDATE(
s,
1
),
VSTACK(
a,
CHOOSE(
{1,
2,
3,
4},
y,
m,
{"Week";"Satur";"Sun"}&"days",
NETWORKDAYS.INTL(
s,
e-1,
{1;"1111101";"1111110"}
)
),
HSTACK(
"Total:",
"",
"",
e-s
)
)
)
)
)Excel solution 2 for Calculate Monthly Day Totals, proposed by Rick Rothstein:
=DROP(REDUCE(0,
SEQUENCE(
ROWS(
A2:B4
)
),
LAMBDA(a,
x,
LET(y,
CHOOSEROWS(
A2:B4,
x
),
f,
0+CONCAT(
y*{1,
-1},
-1
),
e,
EOMONTH(
f,
0
),
d,
SEQUENCE(
e-f+1,
,
f
),
w,
WEEKDAY(
d,
2
),
h,
HSTACK,
v,
VSTACK,
g,
h(v(
y,
y,
y
),
{"Weekdays";"Saturdays";"Sundays"},
-v(SUM(-(w<6)),
SUM(-(w=6)),
SUM(-(w=7)))),
v(
a,
g,
h(
"Total:",
"",
"",
SUM(
TAKE(
g,
,
-1
)
)
)
)))),
1)Excel solution 3 for Calculate Monthly Day Totals, proposed by John V.:
=REDUCE(
D1:G1,
B2:B4,
LAMBDA(
a,
v,
LET(
b,
@+A4:v,
z,
b&-v&-1,
d,
NETWORKDAYS.INTL(
z,
EOMONTH(
z,
0
),
BASE(
{1;125;126},
2,
7
)
),
VSTACK(
a,
CHOOSE(
{1,
2,
3,
4},
b,
v,
{"Week";"Satur";"Sun"}&"days",
d
),
HSTACK(
"Total:",
"",
"",
SUM(
d
)
)
)
)
)
)
or this (with a little "trick"):
✅
=REDUCE(
D1:G1,
B2:B4,
LAMBDA(
a,
v,
LET(
z,
@+A4:v&-v&-1,
i,
SEQUENCE(
EDATE(
z,
1
)-z,
,
z
),
VSTACK(
a,
GROUPBY(
HSTACK(
YEAR(
i
),
MONTH(
i
),
SWITCH(
MOD(
i,
7
),
,
"Satur",
1,
" Sun",
"Week"
)&"days"
),
i,
ROWS,
,
,
-3
)
)
)
)
)Excel solution 4 for Calculate Monthly Day Totals, proposed by Kris Jaganah:
=LET(
a,
SEQUENCE(
731,
,
DATE(
2024,
1,
1
)
),
b,
YEAR(
a
),
c,
MONTH(
a
),
d,
SWITCH(
WEEKDAY(
a
),
7,
"Saturdays",
1,
"Sundays",
"Weekdays"
),
REDUCE(
{"Year",
"Month",
"Type",
"Days"},
DATE(
A2:A4,
B2:B4,
1
),
LAMBDA(
x,
y,
VSTACK(
x,
GROUPBY(
HSTACK(
b,
c,
d
),
d,
COUNTA,
,
1,
-4,
y=DATE(
b,
c,
1
)
)
)
)
)
)Excel solution 5 for Calculate Monthly Day Totals, proposed by Timothée BLIOT:
=LET(V,
VSTACK,
H,
HSTACK,
F,
LAMBDA(y,
m,
LET(A,
DATE(
y,
m,
1
),
B,
EOMONTH(
A,
0
),
S,
WEEKDAY(
SEQUENCE(
B-A+1,
,
A
),
2
),
IFNA(H(V(
y,
y,
y,
"Total:"
),
V(
m,
m,
m
),
{"Weekdays";"Saturdays";"Sundays"},
V(SUM(--(S<6)),
SUM(--(S=6)),
SUM(--(S=7)),
COUNT(
S
))),
""))),
V(
F(
A2,
B2
),
F(
A3,
B3
),
F(
A4,
B4
)
))Excel solution 6 for Calculate Monthly Day Totals, proposed by Oscar Mendez Roca Farell:
=REDUCE(D1:G1,B2:B4,LAMBDA(i,x,LET(s,SEQUENCE(,31),f,1&-x&-@+A4:x,d,TOCOL(f+IFS(EOMONTH(f,0)-f+1>=s,s-1),2),w,WEEKDAY(d,2),VSTACK(i,GROUPBY(HSTACK(YEAR(d),MONTH(d),IFS(w=6,"Satur",w=7,"Sun",1,"Week")&"days"),d,ROWS)))))Excel solution 7 for Calculate Monthly Day Totals, proposed by Abdallah Ally:
=REDUCE(
{"Year",
"Month",
"Type",
"Days"},
A2:A4,
LAMBDA(
x,
y,
LET(
a,
COUNT,
b,
OFFSET(
y,
,
1
),
& c,
DATE(
y,
b,
1
),
d,
EOMONTH(
c,
0
),
e,
SEQUENCE(
d-c+1,
,
c
),
f,
a(
e
),
g,
a(
FILTER(
e,
WEEKDAY(
e,
3
)<5
)
),
h,
a(
FILTER(
e,
WEEKDAY(
e,
3
)=5
)
),
i,
f-g-h,
j,
HSTACK,
VSTACK(
x,
j(
y,
b,
"Weekdays",
g
),
j(
y,
b,
"Saturdays",
h
),
j(
y,
b,
"Sundays",
i
),
j(
"Total:",
"",
"",
f
)
)
)
)
)Excel solution 8 for Calculate Monthly Day Totals, proposed by Md. Zohurul Islam:
=LET(u,A2:B4,p,HSTACK("Year","Month","Type","Days"),
q,BYROW(u,LAMBDA(x,TEXTJOIN("/",1,x))),
result,REDUCE(p,q,LAMBDA(y,x,LET(a,ABS(TEXTBEFORE(x,"/")),b,ABS(TEXTAFTER(x,"/")),d,DATE(a,b,1),e,EOMONTH(d,0),num,e-d+1,dt,TEXT(SEQUENCE(num,,d),"ddd"),g,SUM(ABS(dt="Sat")),h,SUM(ABS(dt="Sun")),w,SUM(NOT(dt="Sat")*NOT(dt="Sun")),p,HSTACK("Weekdays",w),q,HSTACK("Saturdays",g),r,HSTACK("Sundays",h),s,HSTACK(a,b),m,IFNA(HSTACK(s,VSTACK(p,q,r)),s),n,HSTACK("Total:","","",num),rng,VSTACK(m,n),z,VSTACK(y,rng),z))),result)Excel solution 9 for Calculate Monthly Day Totals, proposed by Hamidi Hamid:
=LET(s,A2:A4,l,SEQUENCE(ROWS(s)),m,B2:B4,f,LAMBDA(a,TOCOL(HSTACK(TEXTSPLIT(REPT(a&"-",SEQUENCE(,ROWS(a))),,"-",),IF(l,":")))),n,EOMONTH(m&"/"&m&"/"&s,-1)+1,k,EOMONTH(m&"/"&m&"/"&s,0),y,MAP(k,n,LAMBDA(a,b,ARRAYTOTEXT(NETWORKDAYS.INTL(b,a,{"0000011","1111101","1111110"})))),x,DROP(REDUCE(0,y,LAMBDA(a,b,VSTACK(a,TEXTSPLIT(b,",",)))),1)*1,HSTACK(IFERROR(f(s)*1,"Total :"),IFERROR(f(m)*1,""),TOCOL(HSTACK(TEXTSPLIT(REPT({"Weekdays","Saturdays","Sundays"}&"-",l),,"-",),IF(l,""))),TOCOL(HSTACK(x,BYROW(x,SUM)))))Excel solution 10 for Calculate Monthly Day Totals, proposed by ferhat CK:
=REDUCE(
D1:G1,
A2:A4,
LAMBDA(
x,
y,
VSTACK(
x,
LET(
tb,
DATE(
y,
OFFSET(
y,
,
1
),
"01"
),
ts,
EDATE(
tb,
1
),
t,
SEQUENCE(
ts-tb,
,
tb
),
g,
BYROW(
WEEKDAY(
t,
3
),
LAMBDA(
r,
IFS(
r<5,
"Weekday",
r=5,
"Saturdays",
r=6,
"Sunday"
)
)
),
k,
GROUPBY(
g,
g,
COUNTA
),
IFNA(
HSTACK(
SEQUENCE(
3
)^0*y,
SEQUENCE(
3
)^0*OFFSET(
y,
,
1
),
k
),
""
)
)
)
)
)Excel solution 11 for Calculate Monthly Day Totals, proposed by Jaroslaw Kujawa:
=REDUCE(HSTACK(
A1;
B1;
"Type";
"Days"
);
B2:B4;
LAMBDA(a;
x;
LET(year;
IF(
x>10;
2024;
2025
);
yr;
VSTACK(
year;
year;
year;
"Total:"
);
mo;
VSTACK(
x;
x;
x;
""
);
type;
VSTACK(
"Weekdays";
"Saturdays";
"Sundays";
""
);
dt;
DATE(
year;
x;
SEQUENCE(
DATE(
year;
x+1;
0
)-DATE(
year;
x;
1
)+1
)
);
st;
SUM(1*(WEEKDAY(
dt;
2
)=6));
sd;
SUM(1*(WEEKDAY(
dt;
2
)=7));
VSTACK(
a;
HSTACK(
yr;
mo;
type;
VSTACK(
MAX(
DAY(
dt
)
)-st-sd;
st;
sd;
MAX(
DAY(
dt
)
)
)
)
))))Excel solution 12 for Calculate Monthly Day Totals, proposed by Md Ismail Hosen:
=LAMBDA(YearAndMonthSeq,
LET(_CurrentYearAndMonthSeq,
LAMBDA(year,
month,
LET(_StartDate,
DATE(
year,
month,
1
),
_EndDate,
EOMONTH(
_StartDate,
0
),
_Days,
TEXT(
SEQUENCE(
_EndDate-_StartDate+1,
1,
_StartDate
),
"dddd"
),
_Step4,
IF((_Days<>"Sunday")*(_Days<>"Saturday"),
"Weekdays",
_Days&"s"),
_GroupedData,
SUBSTITUTE(
GROUPBY(
_Step4,
_Step4,
COUNTA,
0
),
"Total",
""
),
_Sorted,
SORTBY(
_GroupedData,
MATCH(
TAKE(
_GroupedData,
,
1
),
{"Weekdays";"Saturdays";"Sundays";""},
0
),
1
),
_Result,
HSTACK(
VSTACK(
EXPAND(
year,
ROWS(
_Sorted
)-1,
1,
year
),
"Total:"
),
VSTACK(
EXPAND(
month,
ROWS(
_Sorted
)-1,
1,
month
),
""
),
TAKE(
_Sorted,
,
1
),
TAKE(
_Sorted,
,
-1
)*1
),
_Result)),
_Result,
REDUCE(
{"Year",
"Month",
"Type",
"Days"},
SEQUENCE(
ROWS(
YearAndMonthSeq
)
),
LAMBDA(
Acc,
Curr,
VSTACK(
Acc,
_CurrentYearAndMonthSeq(
INDEX(
YearAndMonthSeq,
Curr,
1
),
INDEX(
YearAndMonthSeq,
Curr,
2
)
)
)
)
),
_Result))(A2:B4)Solving the challenge of Calculate Monthly Day Totals with Python
Python solution 1 for Calculate Monthly Day Totals, proposed by Konrad Gryczan, PhD:
import pandas as pd
import calendar
path = "595 List Weekdays Saturdays and Sundays and Total.xlsx"
input = pd.read_excel(path, usecols="A:B", nrows=3)
test = pd.read_excel(path, usecols="D:G", nrows=13).rename(columns=lambda x: x.split('.')[0]).fillna('')
def get_day_counts(year, month):
month_calendar = calendar.monthcalendar(year, month)
counts = [sum(1 for week in month_calendar if week[i] != 0) for i in range(7)]
total_days = sum(counts)
return [
{'Year': year, 'Month': month, 'Type': 'Weekdays', 'Days': sum(counts[:5])},
{'Year': year, 'Month': month, 'Type': 'Saturdays', 'Days': counts[5]},
{'Year': year, 'Month': month, 'Type': 'Sundays', 'Days': counts[6]},
{'Year': 'Total:', 'Month': '', 'Type': '', 'Days': total_days}
]
result = pd.DataFrame([day for _, row in input.iterrows() for day in get_day_counts(row['Year'], row['Month'])])
print(result.equals(test)) # True
Python solution 2 for Calculate Monthly Day Totals, proposed by Abdallah Ally:
import pandas as pd
from datetime import date
from calendar import monthrange
file_path = 'Excel_Challenge_595 - List Weekdays Saturdays and Sundays and Total.xlsx'
df = pd.read_excel(file_path, usecols='A:B', nrows=3)
# Perform data manipulation
values = []
for year, month in zip(df['Year'], df['Month']):
days = [
date(year, month, day).weekday()
for day in range(1, monthrange(year, month)[1] + 1)
]
value = [
[year, month, 'Weekdays', sum(d < 5 for d in days)],
[year, month, 'Saturdays', sum(d == 5 for d in days)],
[year, month, 'Sundays', sum(d == 6 for d in days)],
['Total:', '', '', len(days)],
]
values.extend(value)
df = pd.DataFrame(values, columns=list(df.columns) + ['Type', 'Days'])
df
Python solution 3 for Calculate Monthly Day Totals, proposed by Artur Pilipczuk:
path=r"Excel_Challenge_595 - List Weekdays Saturdays and Sundays and Total.xlsx"
import polars as pl
pl.Config.set_fmt_table_cell_list_len(1000)
pl.Config.set_tbl_rows(-1)
df=pl.read_excel(path,sheet_name="Sheet1",columns="A:B")
df=(df.with_columns(daty=pl.date_ranges(pl.date("Year","Month",1),pl.date("Year",pl.col("Month"),1).dt.month_end()).list.eval(pl.all().dt.weekday())
)
.with_columns(AWeekdays=pl.col("daty").list.eval(pl.element() < 6).list.sum(),
Saturdays=pl.col("daty").list.count_matches(6),
Sundays=pl.col("daty").list.count_matches(7),)
.drop("daty")
.unpivot(index=["Year","Month"],variable_name="Type",value_name="Days")
.sort(df.columns)
.with_columns(pl.col("Year").cast(pl.Utf8),pl.col("Month").cast(pl.Utf8),pl.col("Days").cast(pl.Int64)))
dfg=(df.group_by("Year","Month").agg(pl.sum("Days")).with_columns(Type=pl.lit("Z"))).select(df.columns)
dffinal=pl.DataFrame(dfg.filter(pl.col("Year")=="-1"))
for row in dfg.iter_rows():
dffinal=(dffinal.vstack(df.filter(pl.col("Year")==row[0],pl.col("Month")==row[1]))
.vstack(pl.DataFrame({
"Year": "Total:",
"Month": "",
"Type": "",
"Days": row[3]
})))
dffinal=dffinal.with_columns(pl.col("Type").str.replace("A",""))
print(dffinal)
Solving the challenge of Calculate Monthly Day Totals with Python in Excel
Python in Excel solution 1 for Calculate Monthly Day Totals, proposed by Alejandro Campos:
import calendar
df = xl("A1:B4", headers=True)
def calculate_days(y, m):
d = calendar.monthrange(y, m)[1]
return d, sum(calendar.weekday(y, m, i) < 5 for i in range(1, d+1)), d//7 +
(calendar.weekday(y, m, 1) == 5), d//7 + (calendar.weekday(y, m, 1) == 6)
df[['Total Days', 'Weekdays', 'Saturdays', 'Sundays']] = df.apply(
lambda row: calculate_days(row['Year'], row['Month']), axis=1, result_type='expand')
formatted_data = []
for index, row in df.iterrows():
formatted_data.append([row['Year'], row['Month'], 'Weekdays', row['Weekdays']])
formatted_data.append([row['Year'], row['Month'], 'Saturdays', row['Saturdays']])
formatted_data.append([row['Year'], row['Month'], 'Sundays', row['Sundays']])
formatted_data.append(['Total:', '', '', row['Total Days']])
formatted_df = pd.DataFrame(formatted_data, columns=['Year', 'Month', 'Type', 'Days'])
formatted_df
Python in Excel solution 2 for Calculate Monthly Day Totals, proposed by Anshu Bantra:
import datetime as dtt
import calendar as cal
df = xl("A1:B4", headers=True)
def get_weekdays(row: pd.Series) -> list[str]:
lst = [ dtt.date(row['Year'], row['Month'], day).strftime('%A')
for day in range(1, cal.monthrange(row['Year'], row['Month'])[1]+1)
]
return ['Weekdays' if day not in ['Saturday', 'Sunday'] else day for day in lst]
df['Type'] = df.apply(get_weekdays, axis=1)
df = df.explode('Type').reset_index(drop=True)
df = df.value_counts().sort_values(ascending=False).reset_index(name='Days')
df = df.sort_values(by=['Year', 'Month', 'Days'], ascending=[True, True, False]).reset_index(drop=True)
df['Total'] = 0
subtotals = df.groupby(['Year', 'Month'])['Days'].sum().reset_index()
subtotals['Type'] = 'Total'
subtotals['Total'] = 1
df = pd.concat([df, subtotals], ignore_index=True).
sort_values(by=['Year', 'Month', 'Total', 'Days'], ascending=[True, True, True, False])
df.drop('Total', inplace=True, axis=1)
df['Year'] = np.where(df['Type']=='Total', ['Total: '], df['Year'])
df['Month'] = np.where(df['Type']=='Total', [''], df['Month'])
df['Type'] = np.where(df['Type']=='Total', [''], df['Type'])
df.reset_index(drop=True)
Python in Excel solution 3 for Calculate Monthly Day Totals, proposed by Anshu Bantra:
import calendar as cal
import datetime as dtt
def generate_dates(year, month):
num_days = cal.monthrange(year, month)[1]
return [dtt.datetime(year, month, day) for day in range(1, num_days + 1)]
df = xl("A1:B4", headers=True)
for idx, row in df.iterrows():
if idx == 0:
new_df = pd.DataFrame(generate_dates(row['Year'], row['Month']), columns=['Dates'])
else:
new_df = pd.concat([new_df, pd.DataFrame(generate_dates(row['Year'], row['Month']), columns=['Dates'])])
new_df['Year'] = new_df['Dates'].dt.year
new_df['Month'] = new_df['Dates'].dt.month
new_df['DOW'] = new_df['Dates'].dt.dayofweek
new_df['Type'] = np.where( new_df['DOW']<=4, "Weekdays",
np.where(new_df['DOW']==5, 'Saturday', 'Sunday') )
grp = new_df.groupby(by = ['Year', 'Month', 'Type']).size().reset_index(name='Days')
result = pd.DataFrame(columns=grp.columns)
for (Year, Month), gp in grp.groupby(['Year', 'Month']):
result = pd.concat([result, gp.sort_values(by='Days', ascending=False)], ignore_index=True)
total = gp['Days'].sum()
result = pd.concat([result, pd.DataFrame({'Year':['Total:'], 'Month':[''], 'Type':[''], 'Days':[gp['Days'].sum()]})], ignore_index=True)
result
Solving the challenge of Calculate Monthly Day Totals with R
R solution 1 for Calculate Monthly Day Totals, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
library(janitor)
path = "Excel/595 List Weekdays Saturdays and Sundays and Total.xlsx"
input = read_excel(path, range = "A1:B4")
test = read_excel(path, range = "D1:G13")
result = input %>%
mutate(start_of_month = make_date(Year, Month, 1),
end_of_month = make_date(Year, Month, 1) %>% ceiling_date("month") - 1) %>%
mutate(seq = map2(start_of_month, end_of_month, seq, by = "days")) %>%
unnest(seq) %>%
mutate(Type = wday(seq, label = T, locale = "en", abbr = F),
Type = case_when(Type == "Sunday" ~ "Sundays",
Type == "Saturday" ~ "Saturdays",
TRUE ~ "Weekdays") %>% factor(., levels = c("Weekdays", "Saturdays", "Sundays"), ordered = T)) %>%
summarise(Days = n(), .by = c(Year, Month, Type)) %>%
arrange(Year, Month, Type) %>%
group_by(Year, Month) %>%
group_modify(~ .x %>% adorn_totals("row")) %>%
ungroup() %>%
mutate(Year = ifelse(Type == "Total", "Total:", Year),
Month = ifelse(Type == "Total", NA, Month),
Type = ifelse(Type == "Total", NA, as.character(Type)))
all.equal(result, test, check.attributes = F)
#> [1] TRUE
&&