Home » Return the Most Recent Day of Week

Return the Most Recent Day of Week

Return a Date of the most recent/next day of the week starting from Today Strictly Legacy Array Functions or PowerQuery

📌 Challenge Details and Links
Challenge Number: 5
Challenge Difficulty: ⭐⭐
📥Download Sample File
📥Link to the solutions on LinkedIn

Solving the challenge of Return the Most Recent Day of Week with Power Query

Power Query solution 1 for Return the Most Recent Day of Week, proposed by Alejandro Simón 🇵🇦 🇪🇸: 
let
 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 Sol = Table.AddColumn(Source, "Custom", (x)=> 
 let 
 a = List.Dates(
hashtag
#date(2024,1,1), 30, Duration.From(1)),
 b = if x[#"Recent/Past"] = "Recent" then List.Last(List.Select(List.Select(a, each Date.DayOfWeekName(_, "En-us")=x[Day]), each _ < Date.From(DateTime.FixedLocalNow()))) else 
 if x[#"Recent/Past"] = "Next" then List.Select(List.Select(a, each Date.DayOfWeekName(_, "En-us")=x[Day]), each _ > Date.From(DateTime.FixedLocalNow())){0} else Date.From(DateTime.FixedLocalNow())
 in b)
in
 Sol
Power Query solution 2 for Return the Most Recent Day of Week, proposed by Brian Julius: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Tod = Number.From(Date.From(DateTime.FixedLocalNow())), 
  NumsList = {(Tod - 7) .. (Tod + 7)}, 
  DateList = List.Transform(NumsList, each Date.From(_)), 
  LabelList = List.Transform(
    NumsList, 
    each if _ < Tod then "Recent" else if _ > Tod then "Next" else "zz"
  ), 
  Days = List.Transform(
    DateList, 
    each if Number.From(_) = Tod then "Today- " & Date.DayOfWeekName(_) else Date.DayOfWeekName(_)
  ), 
  Table2 = Table.FromColumns({LabelList, Days, DateList}, {"Temporal", "Days", "Date"}), 
  TempSource = Table.ReplaceValue(Source, null, "zz", Replacer.ReplaceValue, {"Recent/Past"}), 
  Join = Table.Join(
    TempSource, 
    {"Recent/Past", "Day"}, 
    Table2, 
    {"Temporal", "Days"}, 
    JoinKind.LeftOuter
  ), 
  Final = Table.RemoveColumns(
    Table.ReplaceValue(Join, "zz", null, Replacer.ReplaceValue, {"Recent/Past"}), 
    {"Temporal", "Days"}
  )
in
  Final
Power Query solution 3 for Return the Most Recent Day of Week, proposed by Nelson Mwangi: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  DaysOfTheWeekList = {
    "Sunday, 0", 
    "Monday, 1", 
    "Tuesday, 2", 
    "Wednesday, 3", 
    "Thursday, 4", 
    "Friday, 5", 
    "Saturday, 6"
  }, 
  WeekTable = Table.FromList(
    DaysOfTheWeekList, 
    Splitter.SplitTextByDelimiter(","), 
    {"DayOfWeek", "Day Number"}
  ), 
  DataType = Table.TransformColumnTypes(WeekTable, {{"Day Number", Int64.Type}}), 
  JoinTables = Table.Join(Source, "Day", DataType, "DayOfWeek", JoinKind.LeftOuter), 
  CalcDays = Table.AddColumn(
    JoinTables, 
    "Days Past/Next", 
    each 
      if [#"Recent/Past"] = "Recent" then
        [Day Number] - 7
      else if [#"Recent/Past"] = "Next" then
        [Day Number]
      else
        0
  ), 
  Date = Table.AddColumn(
    CalcDays, 
    "Date", 
    each Date.AddDays(Date.From(DateTime.FixedLocalNow()), [#"Days Past/Next"])
  ), 
  DeleteCols = Table.RemoveColumns(Date, {"Day Number", "Days Past/Next"})
in
  DeleteCols

Solving the challenge of Return the Most Recent Day of Week with Excel

Excel solution 1 for Return the Most Recent Day of Week, proposed by Rick Rothstein: 
=D$6-WEEKDAY(
   D$6,
   2)-1+7*(LEFT(
   B4)="N")+(FIND(
   LEFT(
       C4,
       2),
   "SuMoTuWeThFrSa")+1)
Excel solution 2 for Return the Most Recent Day of Week, proposed by محمد حلمي: 
=TODAY()+IF(B4>0,
   (LEFT(
       B4)="r")*-7-1+
MATCH(
   C4,
   TEXT(
       ROW(
           $1:$7),
       "dddd"),
   ))

Leave a Reply