Develop Broadcast Calendar for 2025. The broadcast calendar is a standardized calendar used primarily for the planning and purchase of radio and television programs and advertising. Rules to develop – 1. A week starts from Monday and ends on Saturday. 2. First week of every month will contain date 1st of that month. 3. The number of weeks in a broadcast month is based on the number of Sundays that fall in that month.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 695
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Create 2025 Broadcast Calendar with Power Query
Power Query solution 1 for Create 2025 Broadcast Calendar, proposed by Kris Jaganah:
let
A = 2025,
B = (r)=> hashtag#duration(r,0,0,0),
C = List.Dates( List.Select (List.Dates(hashtag#date(A-1,12,25) , 7 , B(1)) ,
(v)=> Date.DayOfWeek(v) = 0 ){0} , 52 ,B(7)),
D = List.Dates( C{0}+B(6),52, B(7)),
E = List.Transform( D , each Date.ToText(_ ,[Format = "MMM"])),
F = List.Count(E),
G = List.Generate(()=> [a = 0 , b = E{a} , c = 1 ] ,
each [a] Power Query solution 2 for Create 2025 Broadcast Calendar, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = List.Dates(hashtag#date(2025,1,1),364, hashtag#duration(1,0,0,0)),
Weeks = List.Distinct(List.Transform(Source, each {Date.StartOfWeek(_,1), Date.EndOfWeek(_,1)})),
Tbl = Table.FromRows(List.Transform(Weeks, each
{Text.Start(Date.MonthName(_{1}, "en-US"),3), Date.WeekOfYear(_{1})-1, _{0}, _{1}}),
{"Month", "Month Week", "From Date", "To Date"}),
#"2025" = Table.SelectRows(Tbl, each Date.Year([To Date])=2025),
Sol = Table.Combine(Table.Group(#"2025", {"Month"}, {{"A", each
let
a = _,
b = Table.AddIndexColumn(a, "Year Week", 1)
[[Month], [Month Week], [Year Week], [From Date], [To Date]]
in b}},0)[A])
in
Sol
Power Query solution 3 for Create 2025 Broadcast Calendar, proposed by Antriksh Sharma:
let
Years = { 2025, 2026, 2027 },
Fx = ( Year ) =>
let
BroadcastList = List.Generate (
() => [ Start = Date.StartOfWeek ( hashtag#date ( Year, 01, 01 ), Day.Monday ), End = Date.EndOfWeek ( Start, Day.Monday ), WeekCounter = 1 ],
each [Start] < Date.StartOfWeek ( Date.EndOfYear ( hashtag#date ( Year, 01, 01 ) ), Day.Monday ),
each [ Start = Date.AddDays ( [End], 1 ), End = Date.EndOfWeek ( Start, Day.Monday ), WeekCounter = if Date.Month ( End ) <> Date.Month ( Start ) or Date.Day ( Start ) = 1 then 1 else [WeekCounter] + 1 ],
each [ From = [Start], To = [End], Month Week = [WeekCounter] ]
),
AddMonth = Table.AddColumn ( Table.FromRecords ( BroadcastList, type table [ From = date, To = date, Month Week = Int64.Type ] ), "Month", each Text.Start ( Date.MonthName ( [To] ), 3 ), type text ),
AddYearWeek = Table.AddIndexColumn ( AddMonth, "Year Week", 1, 1, Int64.Type )
in
AddYearWeek,
Broadcast = List.Transform ( Years, Fx ),
Combine = Table.Combine ( Broadcast )
in
Combine
Power Query solution 4 for Create 2025 Broadcast Calendar, proposed by Peter Krkos:
let
Source = Table.FromList(List.Dates(hashtag#date(2024,12,30), 367, hashtag#duration(1,0,0,0)), (x)=> {x}, type table[From Date=date]),
Grouped = Table.SelectRows(
Table.Group(Source, "From Date", {{"B", each _},
{"Month", each Date.ToText(List.Last([From Date]), [Format="MMM", Culture="en-US"]), type text},
{"To Date", each List.Last([From Date]), type date}},
0, (x,y)=> Byte.From(Date.DayOfWeek(y, Day.Monday) = 0)),
each Table.RowCount([B]) = 7),
Ad_MonthWeek = Table.Combine(Table.Group(Grouped, {"Month"}, {{"T", each Table.AddIndexColumn(_, "Month Week", 1, 1, Int64.Type), type table}})[T]),
Ad_YearWeek = Table.SelectColumns(Table.AddIndexColumn(Ad_MonthWeek, "Year Week", 1, 1, Int64.Type), {"Month", "Month Week", "Year Week", "From Date", "To Date"})
in
Ad_YearWeek
Power Query solution 5 for Create 2025 Broadcast Calendar, proposed by Alexandre Garcia:
((x)=>
[
A = {"Month", "Month Week", "Year Week", "From Date", "To Date"},
B = Table.FromRows(List.Transform({1..52}, each ((x)=> {Date.ToText(List.Max(x), [Format = "MMM", Culture = "en-US"]), _, x{0}, x{1}})(((z)=> {Date.StartOfWeek(z,1), Date.EndOfWeek(z,1)})
C = Table.Combine(Table.Group(B, A{0}, {"x", (x)=> Table.SelectColumns(Table.AddColumn(x, A{1}, each Table.PositionOf(x,_)+1), A)})[x])
] [C]) (2025)
Power Query solution 6 for Create 2025 Broadcast Calendar, proposed by Maciej Kopczyński:
let
startDate = hashtag#date(2024, 12, 30),
monthNames = List.Transform({1..12}, each Text.Start(Text.From(Date.MonthName(Date.From(Text.From(_) & "." & "2025"), "en-US")), 3)),
monthSundayN = List.Transform({1..12}, each List.Count(List.Select(List.Dates(hashtag#date(2025, _, 1), Date.DaysInMonth(hashtag#date(2205, _, 1)), hashtag#duration(1, 0, 0, 0)), each Date.DayOfWeek(_, Day.Monday) = 6))),
monthSeq = List.Transform(monthSundayN, each {1.._}),
A = Table.FromColumns({monthNames, monthSeq}, {"Month", "Month Week"}),
B = Table.ExpandListColumn(A, "Month Week"),
C = Table.AddIndexColumn(B, "Year Week", 1, 1, Int64.Type),
D = Table.AddColumn(C, "From Date", each Date.AddDays(startDate,([Year Week] - 1) * 7)),
E = Table.AddColumn(D, "To Date", each Date.AddDays(startDate, 7 * [Year Week] - 1))
in
E
Power Query solution 7 for Create 2025 Broadcast Calendar, proposed by Aleksandar Kovacevic:
let
C = "en-US",
F = [Format = "dd-MMM-yy"],
Tbl =
Table.ExpandRecordColumn(
Table.AddColumn(
Table.FromList( List.Dates( hashtag#date( 2025, 1, 1 ), 365, Duration.From( 1 ) ), Splitter.SplitByNothing() , {"A"} ),
"B", each
[
WY = Date.WeekOfYear( [A]),
Month Week = Date.WeekOfMonth( [A] ),
WS = Date.StartOfWeek( [A], 1 ),
WE = Date.EndOfWeek( [A], 1 ),
Month = Text.Start( Date.MonthName( [A], C ), 3 )
]
),
"B", { "Month Week", "WY", "WS", "WE", "Month" }
),
Res =
Table.Group(
Table.SelectRows( Tbl, each Text.Start(Date.MonthName( [WE], C ), 3 ) = [Month] ),
{ "Month", "Month Week" },
{
{ "Year Week", each _[WY]{0} },
{ "From Date", each Date.ToText( _[WS]{0}, F, C ) },
{ "To Date", each Date.ToText( _[WE]{0}, F, C ) }
}
)
in
Res
Solving the challenge of Create 2025 Broadcast Calendar with Excel
Excel solution 1 for Create 2025 Broadcast Calendar, proposed by Bo Rydobon 🇹🇭:
=LET(a,"1/1",w,WEEKDAY(a,2),d,SEQUENCE(52+(w+(MONTH(a+59)=2))/6,,a-w+7,7),HSTACK(TEXT(d,"mmm"),ROUNDUP(DAY(d)/7,),(d-@d)/7+1,d-6,d))Excel solution 2 for Create 2025 Broadcast Calendar, proposed by John V.:
=LET(s,ROW(1:52),i,45655+7*s,HSTACK(TEXT(i,"mmm"),-INT(-DAY(i)/7),s,i-6,i))Excel solution 3 for Create 2025 Broadcast Calendar, proposed by 🇰🇷 Taeyong Shin:
=LET(s,"1-1",d,WORKDAY.INTL(s-WEEKDAY(s,2),SEQUENCE(NETWORKDAYS.INTL(s,EDATE(s,12)-1,"1111110")),"0111111")+6,w,WEEKNUM(d,2),HSTACK(TEXT(d,"mmm"),w-WEEKNUM(EOMONTH(d,-1)+1,2)+1,w,d-6,d))Excel solution 4 for Create 2025 Broadcast Calendar, proposed by Alejandro Campos:
=LET(
start,
DATE(
2024,
12,
30
),
weeks,
52,
fromDates,
SEQUENCE(
weeks,
,
start,
7
),
toDates,
fromDates + 6,
months,
TEXT(
fromDates,
"b1mmm"
),
yearWeeks,
SEQUENCE(
weeks
),
monthWeeks,
BYROW(
fromDates,
LAMBDA(d,
SUM((TEXT(
fromDates,
"mmmm"
) = TEXT(
d,
"mmmm"
)) * (fromDates <= d)))),
HSTACK(
months,
monthWeeks,
yearWeeks,
fromDates,
toDates
))Excel solution 5 for Create 2025 Broadcast Calendar, proposed by Timothée BLIOT:
=LET(A,DATE(2024,12,30),B,SEQUENCE(52,,0,7),C,A+B+6,D,B/7+1,E,TEXT(C,"mmm"),HSTACK(E,MAP(D,LAMBDA(x,SUM(--(INDEX(E,x)=TAKE(E,x))))),D,A+B,C))Excel solution 6 for Create 2025 Broadcast Calendar, proposed by Hussein SATOUR:
=LET(a,"1/2025",b,a-WEEKDAY(a,2)+1,c,b+366-WEEKDAY(b+366,2),d,SEQUENCE(c-b+1,,b),e,DROP(GROUPBY(ISOWEEKNUM(d),d,HSTACK(MIN,MAX),,0),1),
HSTACK(TEXT(INDEX(e,,3),"mmm"),ROUNDUP(DAY(INDEX(e,,3))/7,0),e))Excel solution 7 for Create 2025 Broadcast Calendar, proposed by Oscar Mendez Roca Farell:
=LET(s,
ROW(
1:52
),
i,
"1-1",
f,
i-WEEKDAY(
i,
3
)+(s-1)*7,
t,
f+6,
HSTACK(TEXT(
t,
"b1mmm"
),
INT((DAY(
t
)-1)/7)+1,
s,
f,
t))Excel solution 8 for Create 2025 Broadcast Calendar, proposed by Sunny Baggu:
=LET(
_a,
DATE(
2025,
1,
1
),
_s,
_a + SEQUENCE(
365,
,
0
) - (WEEKDAY(
_a
) - 2),
_wd,
WEEKDAY(
--_s
),
_e,
FILTER(
_s,
_wd = 1
),
_st,
TAKE(
FILTER(
_s,
_wd = 2
),
ROWS(
_e
)
),
_yw,
WEEKNUM(
--_e
) - 1,
_m,
TEXT(
_e,
"mmm"
),
_mw,
SCAN(
0,
VSTACK(
1,
N(
DROP(
_m,
1
) = DROP(
_m,
-1
)
)
),
LAMBDA(
a,
v,
IF(
v = 0,
1,
a + v
)
)
),
HSTACK(
_m,
_mw,
_yw,
_st,
_e
)
)Excel solution 9 for Create 2025 Broadcast Calendar, proposed by LEONARD OCHEA 🇷🇴:
=LET(S,SEQUENCE,i,"1/1"-S(7),n,S(52),t,7*n+MAX(IF(WEEKDAY(i)=2,i))-1,HSTACK(TEXT(t,"mmm"),INT((DAY(t)-1)/7)+1,n,t-6,t))Excel solution 10 for Create 2025 Broadcast Calendar, proposed by Anshu Bantra:
=LET(
jan_1_,
DATE(
2025,
1,
1
),
start_,
jan_1_ - (WEEKDAY(
jan_1_,
2
) - 1),
from_,
SEQUENCE(
52,
,
start_,
7
),
months_,
TEXT(
MAP(
from_,
LAMBDA(
x,
FILTER(
SEQUENCE(
,
7,
x
),
DAY(
SEQUENCE(
,
7,
x
)
) = MIN(
DAY(
SEQUENCE(
,
7,
x
)
)
)
)
)
),
"MMM"
),
weeks_,
SCAN(
1,
VSTACK(
FALSE,
DROP(
months_,
-1
) = DROP(
months_,
1
)
),
LAMBDA(
a,
v,
IF(
NOT(
v
),
1,
a + v
)
)
),
VSTACK(
{"Month",
"Month Week",
"Year Week",
"From Date",
"To Date"},
HSTACK(
months_,
weeks_,
SEQUENCE(
COUNT(
weeks_
)
),
from_,
from_ + 6
)
)
)Excel solution 11 for Create 2025 Broadcast Calendar, proposed by Md. Zohurul Islam:
=LET(
hdr,
HSTACK(
"Month",
"Month Week",
"Year Week",
"From Date",
"To Date"
),
dt,
DATE(
2025,
1,
1
),
sdt,
dt-WEEKDAY(
dt,
3
),
a,
SEQUENCE(
52,
,
sdt,
7
),
b,
a+6,
m,
TEXT(
b,
"mmm"
),
n,
DROP(
REDUCE(
"",
UNIQUE(
m
),
LAMBDA(
x,
y,
VSTACK(
x,
SEQUENCE(
COUNTA(
FILTER(
m,
m=y
)
)
)
)
)
),
1
),
z,
VSTACK(
hdr,
HSTACK(
m,
n,
SEQUENCE(
52
),
a,
b
)
),
z
)Excel solution 12 for Create 2025 Broadcast Calendar, proposed by Hamidi Hamid:
=LET(x,
WORKDAY.INTL(
SEQUENCE(
366,
,
"24/12/2024",
1
),
1,
11,
),
xu,
UNIQUE(
FILTER(
x,
WEEKDAY(
x,
2
)=1
)
),
xd,
xu+6,
ns,
SEQUENCE(
ROWS(
xu
)
),
nm,
MONTH(
xu
)&"-"&INT(
DAY(
EOMONTH(
xu,
0
)
)/7
),
p,
MAP(nm,
LAMBDA(a,
SUM(N(--(nm=a))))),
k,
TEXTAFTER(
UNIQUE(
nm&"-"&p
),
"-",
-1
)*1,
s,
TOCOL(
DROP(
REDUCE(
0,
k,
LAMBDA(
a,
& b,
VSTACK(
a,
SEQUENCE(
,
b
)
)
)
),
1
),
3
),
HSTACK(
PROPER(
TEXT(
xu,
"mmm"
)
),
s,
ns,
xu,
xd
))Excel solution 13 for Create 2025 Broadcast Calendar, proposed by Asheesh Pahwa:
=LET(d,DATE(2025,1,1),s,SEQUENCE(52,,d-WEEKDAY(d,2)+1,7),
c,s+6,t,TEXT(c,"mmm"),r,DROP(REDUCE("",UNIQUE(t),LAMBDA(x,y,VSTACK(x,SEQUENCE(ROWS(FILTER(t,t=y)))))),1),HSTACK(t,r,SEQUENCE(52),s,c))Excel solution 14 for Create 2025 Broadcast Calendar, proposed by Mihai Radu O:
=REDUCE(
A2:E2,
SEQUENCE(
12
),
LAMBDA(x,
y,
LET(
pz,
DATE(
2025,
y,
1
),
uz,
EOMONTH(
pz,
0
),
nrwk,
NETWORKDAYS.INTL(
pz,
uz,
"1111110"
),
mwk,
SEQUENCE(
nrwk
),
td,
(pz + (7 - WEEKDAY(
pz
) + 1)) + (7 * (mwk - 1)),
fd,
td - 6,
ywk,
WEEKNUM(
td,
2
),
mth,
TEXT(
td,
"MMM"
),
VSTACK(
x,
HSTACK(
mth,
mwk,
ywk,
fd,
td
)
)
)
)
)Excel solution 15 for Create 2025 Broadcast Calendar, proposed by Erdit Qendro:
=LET(st,"01/01/2025",eY,EOMONTH(st,11),
wE,LAMBDA(wE,a,IF((MAX(a)+7)>=eY,a,wE(wE,VSTACK(a,TAKE(a,-1)+7)))),
wkE,wE(wE,st+7-WEEKDAY(st,2)),wkS,wkE-6,
yWN,SEQUENCE(ROWS(wkE)),
mN,TEXT(wkE,"mmm"),
wM,SCAN(0,mN=VSTACK("Jan",DROP(mN,-1)),LAMBDA(a,v,IF(v,a+1,1))),
HSTACK(mN,wM,yWN,wkS,wkE))Excel solution 16 for Create 2025 Broadcast Calendar, proposed by LUIS FLORENTINO COUTO CORTEGOSO:
=LET(s,
SEQUENCE(
52
),
f,
DATEVALUE(
"30/12/24"
)+(s-1)*7,
g,
f+6,
y,
WEEKNUM(
g-1
),
m,
TEXT(
g,
"mmm"
),
w,
s-XMATCH(
m,
m
)+1,
HSTACK(
m,
w,
y,
f,
g
))Excel solution 17 for Create 2025 Broadcast Calendar, proposed by Fredson Alves Pinho:
=MAKEARRAY(52,5,LAMBDA(r,c,CHOOSE(c,TEXT(DATE(2025,1,r*7-2),"mmm"),CEILING(DAY(DATE(2025,1,r*7-2))/7,1),r,DATE(2025,1,r*7-8),DATE(2025,1,r*7-2))))Excel solution 18 for Create 2025 Broadcast Calendar, proposed by Craig Runciman:
=LET(ji,DATE(2025,1,1),dates,SEQUENCE(365,,ji),fm,"dd/mm/yyyy",filt,WEEKDAY(dates)=2,from,FILTER(dates,filt),to,from+6,
k,IF(WEEKDAY(ji)=2,HSTACK(from,to),VSTACK(HSTACK(@from-7,@to-7),HSTACK(from,to))),p,FILTER(k,YEAR(INDEX(k,,2))=2025),arr,MONTH(INDEX(p,,2)),
mw,REDUCE("",INDEX(GROUPBY(arr,arr,COUNT,,0),,2),LAMBDA(a,v,LET(s,VSTACK(a,SEQUENCE(v)),FILTER(s,s<>"")))),m,TEXT(INDEX(p,,2),"mmm"),HSTACK(m,mw,SEQUENCE(ROWS(arr)),TEXT(p,fm)))Solving the challenge of Create 2025 Broadcast Calendar with Python
Python solution 1 for Create 2025 Broadcast Calendar, proposed by Konrad Gryczan, PhD:
import pandas as pd
from datetime import datetime, timedelta
def monday_of_isoweek(year, week):
return datetime(year, 1, 4) + timedelta(days=(week - 1) * 7 - (datetime(year, 1, 4).weekday()))
data = [{
"From": monday_of_isoweek(2025, week),
"To": monday_of_isoweek(2025, week) + timedelta(days=6),
"Week": week
} for week in range(1, 53)]
df = pd.DataFrame(data)
df['Month'] = df['To'].dt.strftime('%b')
df['Month Week'] = df.groupby('Month').cumcount() + 1
df['From'] = df['From'].dt.strftime('%Y-%m-%d')
df['To'] = df['To'].dt.strftime('%Y-%m-%d')
result = df[['Month', 'Month Week', 'Week', 'From', 'To']].rename(columns={
'Month Week': 'Month Week',
'Week': 'Year Week',
'From': 'From Date',
'To': 'To Date'
})
print(result)
Python solution 2 for Create 2025 Broadcast Calendar, proposed by Alejandro Campos:
import calendar as c
from datetime import datetime as d, timedelta as t
def gen_bc(y):
s, wn, bc = d(y-1,12,30), 1, []
for m in range(1,13):
n = c.month_name[m]
while s.month == m or (m == 1 and s.month == 12):
e = s + t(days=6)
bc.append({
"Month": n,
"Month Week": len([x for x in bc if x["Month"] == n]) + 1,
"Year Week": wn,
"From Date": s.strftime("%d-%b-%y"),
"To Date": e.strftime("%d-%b-%y")
})
wn += 1
s = e + t(days=1)
return pd.DataFrame(bc)
broadcast_calendar_df = gen_bc(2025)
Python solution 3 for Create 2025 Broadcast Calendar, proposed by Anshu Bantra:
from datetime import datetime, timedelta
def generate_weekly_calendar(year):
start_date = datetime(year, 1, 1)
start_date -= timedelta(days=start_date.weekday())
end_date = datetime(year, 12, 31)
weeks = []
current_date = start_date
year_week = 1
while current_date <= end_date:
from_date = current_date
to_date = from_date + timedelta(days=6)
month = from_date.strftime('%b') if from_date.day < to_date.day
else to_date.strftime('%b')
month_start = datetime(from_date.year, from_date.month, 1)
first_week_start = month_start - timedelta(days=month_start.weekday())
month_week = ((from_date - first_week_start).days // 7) + 1
weeks.append({
'Month': month,
'Month Week': month_week,
'Year Week': year_week,
'From Date': from_date.strftime('%d-%b-%y'),
'To Date': to_date.strftime('%d-%b-%y')
})
current_date += timedelta(days=7)
year_week += 1
return pd.DataFrame(weeks)
generate_weekly_calendar(2025)
Solving the challenge of Create 2025 Broadcast Calendar with Python in Excel
Python in Excel solution 1 for Create 2025 Broadcast Calendar, proposed by Aditya Kumar Darak 🇮🇳:
from datetime import datetime, timedelta
Y = 2025
mon0 = datetime(Y, 1, 1) - timedelta(days=datetime(Y, 1, 1).weekday())
sunN = datetime(Y, 12, 31) - timedelta(days=(datetime(Y, 12, 31).weekday() + 1) % 7)
rng = pd.date_range(mon0, sunN, freq="W-MON")
wks = [(d, d + timedelta(days=6)) for d in rng]
bms = [
next((x.month for x in pd.date_range(s, e) if x.day == 1 and x.year == Y), e.month)
for s, e in wks
]
mws, cnt = [], {}
for m in bms:
cnt[m] = cnt.get(m, 0) + 1
mws.append(cnt[m])
df = pd.DataFrame(
{
"Mon": [datetime(Y, m, 1).strftime("%b") for m in bms],
"MW": mws,
"YW": range(1, len(wks) + 1),
"From": [s.strftime("%d-%b-%y") for s, _ in wks],
"To": [e.strftime("%d-%b-%y") for _, e in wks],
}
)
Solving the challenge of Create 2025 Broadcast Calendar with R
R solution 1 for Create 2025 Broadcast Calendar, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Excel/695 Broadcast Calendar.xlsx"
test = read_excel(path, range = "A2:E54")
monday_of_isoweek <- function(year, week) {
ISOdate(year, 1, 4) +
weeks(week - 1) -
days(wday(ISOdate(year, 1, 4), week_start = 1) - 1)
}
result = data.frame(
From = monday_of_isoweek(2025, 1:52),
To = monday_of_isoweek(2025, 1:52) + days(6),
Week = 1:52
) %>%
mutate(Month = month(To, label = TRUE, abbr = TRUE, locale = "en")) %>%
mutate(month_week = row_number(), .by = Month) %>%
mutate(From = format(From, "%Y-%m-%d"), To = format(To, "%Y-%m-%d")) %>%
select(
Month,
`Month Week` = month_week,
`Year Week` = Week,
`From Date` = From,
`To Date` = To
)
&&