— CAN YOU SOLVE THIS - EXCEL CHALLENGE 184 —
(Solutions in any language are also welcome for Excel Challenges)
List the years in this century which are having at least 2 months which had 5 Fridays, 5 Saturdays and 5 Sundays.
For example, year 2010 had January and October which met this criterion.
Post answers in Comment.
(Your formula need not be a single formula. You can write multiple formulas to arrive at a solution. Also your formula need not be different from others as long as you have worked out your formula independently)
Download Practice File - https://lnkd.in/dfm2CPTB
#excel, #excelchallenge, #powerquerychallenge, #daxchallenge, #m, #dax, #powerbi, #powerquery, #powerpivot, #sheets, #googlesheets, #data, #analytics, #businessintelligence, #finance, #office365, #python, #r
Excel BI LinkedIn Post
=LET(y,SEQUENCE(100,,2000),FILTER(y,BYROW(WEEKDAY(DATE(y,1,SEQUENCE(,31))),LAMBDA(a,SUM((a=VSTACK(1,6,7))*1)))>=15))
The key is only in the month of January
Rick Rothstein , If it’s about cutting, let’s try this
=LET(n,SEQUENCE(100,,2000),FILTER(n,WEEKDAY(DATE(n,1,1))=6))
It seems that you only need to analyze if the firstL
Hi Everyone… Here’s My PQ Code:
let
LDates = Table.FromList({Number.From([#date](https://www.linkedin.com/feed/hashtag/?keywords=date)(2000,1,1))..Number.From([#date](https://www.linkedin.com/feed/hashtag/?keywords=date)(2099,12,31))}, Splitter.SplitByNothing(), {"Dates"}, null, ExtraValues.Error),
TypeDate = Table.TransformColumnTypes(LDates,{{"Dates", type date}}),
IYear = Table.AddColumn(TypeDate, "Año", each Date.Year([Dates]), Int64.Type),
IMonth = Table.AddColumn(IYear, "Mes", each Date.Month([Dates]), Int64.Type),
NDay = Table.AddColumn(IMonth, "Nombre del día", each Date.DayOfWeekName([Dates]), type text),
FMS = Table.SelectRows(NDay, each ([Nombre del día] = "domingo" or [Nombre del día] = "sábado" or [Nombre del día] = "viernes")),
FGroup = [Table.Group](http://table.group/)(FMS, {"Año", "Mes", "Nombre del día"}, {{"Recuento", each Table.RowCount(_), Int64.Type}}),
FGreater5 = Table.SelectRows(FGroup, each [Recuento] >= 5),
SumRC = [Table.Group](http://table.group/)(FGreater5, {"Año", "Mes"}, {{"Total Series", each List.Sum([Recuento]), type number}}),
Filter15 = Table.SelectRows(SumRC, each [Total Series] >= 15),
GBYears = [Table.Group](http://table.group/)(Filter15, {"Año"}, {{"Recuento", each Table.RowCount(_), Int64.Type}}),
GT2 = Table.SelectRows(GBYears, each [Recuento] >= 2),
Result = Table.RemoveColumns(GT2,{"Recuento"})
in
Result
Five Fri Sat Sun w/ #powerquery. #bitanbit #powerbi
let
Source = DateTime.LocalNow(),
AddCenturyDates = let t = Number.IntegerDivide(Date.Year(Source), 100) * 100 in {Number.From([#date](https://www.linkedin.com/feed/hashtag/?keywords=date)((t+1), 1, 1))..Number.From([#date](https://www.linkedin.com/feed/hashtag/?keywords=date)((t+100), 12, 31))},
ConvertToDate = List.Transform(AddCenturyDates, each Date.From(_)),
ConvertToTable = Table.FromList(ConvertToDate, Splitter.SplitByNothing(), {"Date"}),
FormatDate = Table.TransformColumnTypes(ConvertToTable,{{"Date", type date}}),
InsertedYear = Table.AddColumn(FormatDate, "Year", each Date.Year([Date]), Int64.Type),
InsertedMonth = Table.AddColumn(InsertedYear, "Month", each Date.Month([Date])),
InsertedDayOfWeek = Table.AddColumn(InsertedMonth, "DayOfWeek", each Date.DayOfWeekName([Date], "en-US")),
PivotedDayOfWeek = Table.Pivot(InsertedDayOfWeek, List.Distinct(InsertedDayOfWeek[DayOfWeek]), "DayOfWeek", "Date", List.Count),
Filtered5Days = Table.SelectRows(PivotedDayOfWeek, each ([Friday] = 5) and ([Saturday] = 5) and ([Sunday] = 5)),
GroupedYears = [Table.Group](http://table.group/)(Filtered5Days, {"Year"}, {{"Months", each Table.RowCount(_), Int64.Type}}),
FilteredYears = Table.SelectRows(GroupedYears, each ([Months] >= 2)),
Solution = FilteredYears[Year]
in
Solution
This seems quite long and not elegant but it works
=LET(y,SEQUENCE(100,,2000),FILTER(y,
MAP(y,LAMBDA(years,SUM(--(DROP(REDUCE(0,SEQUENCE(12),
LAMBDA(a,b,LET(alldays,SEQUENCE(DATE(years,12,31)-DATE(years,1,1)+1,,DATE(years,1,1)),
dbm,FILTER(alldays,MONTH(alldays)=b),VSTACK(a,COUNT(FILTER(dbm,ISNUMBER(XMATCH(WEEKDAY(dbm),{1,6,7})))))))),1)=15))>=2))))
In the first column, enter a list of dates in the format “YYYY-MM-DD” that correspond to the months you want to check. For example, you could enter the dates “2023-08-01”, “2023-09-01”, “2023-10-01”, and so on.
In the second column, enter the following formula:
=IF(AND(WEEKDAY(A2)=6,DAY(DATE(YEAR(A2),MONTH(A2)+1,0))-28>=0),TRUE,FALSE)
The formula will return either TRUE or FALSE depending on whether the corresponding month has 5 Fridays, 5 Saturdays, and 5 Sundays.
My hashtag#powerquery solution.
let
StartDate = Number.From( [hashtag#date](https://www.linkedin.com/feed/hashtag/date/)(2000, 1, 1)),
EndDate = Number.From( [hashtag#date](https://www.linkedin.com/feed/hashtag/date/)(2099, 12, 31)),
Source = Table.TransformColumnTypes( Table.FromList({ StartDate..EndDate }, Splitter.SplitByNothing(), null, null, ExtraValues.Error), {"Column1", type date}),
Rename = Table.RenameColumns(Source,{{"Column1", "Dates"}}),
Year = Table.AddColumn(Rename, "Year", each Date.Year([Dates]), Int64.Type),
MonthName = Table.AddColumn(Year, "Month Name", each Date.MonthName([Dates]), type text),
DayName = Table.AddColumn(MonthName, "Day Name", each Date.DayOfWeekName([Dates]), type text),
Group1 = [Table.Group](http://table.group/)(DayName, {"Year", "Month Name", "Day Name"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
Filter1 = Table.SelectRows(Group1, each ([Day Name] = "Friday" or [Day Name] = "Saturday" or [Day Name] = "Sunday") and ([Count] = 5)),
Group2 = [Table.Group](http://table.group/)(Filter1, {"Year", "Month Name"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
Filter2 = Table.SelectRows(Group2, each ([Count] = 3)),
Group3 = [Table.Group](http://table.group/)(Filter2, {"Year"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
Filter3 = Table.SelectRows(Group3, each ([Count] = 2)),
Clean = Table.RemoveColumns(Filter3,{"Count"})
in
Clean
hi everyone, my proposal solution:
=LET(_y, SEQUENCE(100, ,2000), FILTER(_y, MAP(_y, LAMBDA(a, SUM(--(REDUCE("", SEQUENCE( ,12), LAMBDA(i, x ,LET(_f, DATE(a, x, 1), VSTACK(i, MOD( SUM(--(WEEKDAY( SEQUENCE( DAY(EOMONTH(_f, 0)), ,_f), 3)>3)), 5)))))=0))))>1))
