Home » Condition Formatting

Condition Formatting

Condition Format Project days Excluding Weekend e.g Proj A starts 16th for five days but ends on the 22nd since the weekend is not counted Write a dynamic formula to paste into conditional formatting

📌 Challenge Details and Links
Challenge Number: 39
Challenge Difficulty: ⭐⭐
📥Link to the solutions on LinkedIn

Solving the challenge of Condition Formatting with Power Query

Power Query solution 1 for Condition Formatting, proposed by Brian Julius: 
let
 S = Table.TransformColumnTypes( Excel.CurrentWorkbook(){[Name="Table1"]}[Content], {"Start", Date.Type}),
 DList = Table.AddColumn(S, "DateList", each [ 
 a = 2* (Number.Mod( List.Max(S[Days]), 5) + 1),
 b = List.Dates( [Start], [Days] + a, 
hashtag
#duration(1,0,0,0)),
 c = List.Select( b, each Date.DayOfWeek( _, Day.Monday) <= 4),
 d = List.FirstN(c, [Days]) 
 ][d]),
 Expand = Table.ExpandListColumn(DList, "DateList"),
 AddX = Table.AddColumn(Expand, "X", each "X"),
 AddNumList = List.Transform( AddX[DateList], each Number.From(_)),
 a = List.Min( AddNumList),
 b = List.Max( AddNumList ),
 AddTab = List.Transform( {a..b}, each Date.From(_) ),
 ToTab = Table.Join( Table.FromList(AddTab, Splitter.SplitByNothing(), {"Dates"}, null), "Dates", AddX, "DateList", JoinKind.LeftOuter),
 RemCol = Table.TransformColumnTypes( Table.RemoveColumns(ToTab,{"DateList"}), {"Dates", Date.Type}),
 Sort = Table.TransformColumnTypes( Table.Sort(RemCol,{{"Dates", Order.Ascending}, {"Project", Order.Ascending}}), {{"Dates", type text}}),
 Pivot = Table.SelectRows( Table.Pivot(Sort, List.Distinct(Sort[Dates]), "Dates", "X"), each [Project] <> null)
in
 Pivot
Power Query solution 2 for Condition Formatting, proposed by N’rele Ferdinand Attobra: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  ReplaceNull = Table.ReplaceValue(
    Source, 
    null, 
    "x", 
    Replacer.ReplaceValue, 
    Table.ColumnNames(Source)
  ), 
  UnpivotCol = Table.UnpivotOtherColumns(ReplaceNull, {"Project", "Start", "Days"}, "Date", "-"), 
  DType = Table.TransformColumnTypes(
    Table.TransformColumnTypes(
      UnpivotCol, 
      {{"Project", type text}, {"Start", type date}, {"Days", Int64.Type}, {"Date", type datetime}}
    ), 
    {{"Date", type date}}
  ), 
  maxDate = List.Max(DType[Date]), 
  fxDateList = (startD) =>
    Table.SelectRows(
      Table.AddColumn(
        Table.TransformColumnTypes(
          Table.FromList(
            ({Number.From(startD) .. Number.From(maxDate)}), 
            Splitter.SplitByNothing(), 
            {"Date"}
          ), 
          {"Date", type date}
        ), 
        "w", 
        (_) => Date.DayOfWeek(_[Date], Day.Monday) < 5
      ), 
      each [w] = true
    ), 
  EndDate = Table.AddColumn(
    DType, 
    "End", 
    (_) => List.Max(Table.FirstN(fxDateList(_[Start]), _[Days])[Date])
  ), 
  Working = Table.RemoveColumns(
    Table.AddColumn(
      EndDate, 
      "Work?", 
      each Number.From(Date.DayOfWeek([Date], 1) < 5)
        * Number.From([Date] >= [Start])
        * Number.From([Date] <= [End])
    ), 
    {"End", "-"}
  ), 
  Pivot = Table.Pivot(
    Table.TransformColumnTypes(Working, {{"Date", type text}}), 
    List.Distinct(Table.TransformColumnTypes(Working, {{"Date", type text}})[Date]), 
    "Date", 
    "Work?"
  )
in
  Pivot

Solving the challenge of Condition Formatting with Excel

Excel solution 1 for Condition Formatting, proposed by Aditya Kumar Darak 🇮🇳: 
=AND(
   
    E$2 >= WORKDAY(
        $C3,
         0),
   
    E$2 < WORKDAY(
        $C3,
         $D3),
   
    WEEKDAY(
        E$2,
         2) < 6
   )
Excel solution 2 for Condition Formatting, proposed by Oscar Mendez Roca Farell: 
=XMATCH(
   E$2,
    WORKDAY(
        $C3,
         SEQUENCE(
             ,
              $D3)-1))
Excel solution 3 for Condition Formatting, proposed by Ankur Sharma: 
=XMATCH(
   E$2,
    WORKDAY(
        $C3 - 1,
         SEQUENCE(
             $D3)))
Excel solution 4 for Condition Formatting, proposed by Gerson Pineda: 
=AND(
   WEEKDAY(
       E$2,
       2)<6,
   E$2>=$C3,
   SUM(
       $R3:R3)<$D3)
Excel solution 5 for Condition Formatting, proposed by N'rele Ferdinand Attobra: 
=AND(
   E$2<=WORKDAY(
       $C3,
       $D3-1),
   E$2>=$C3,
   WEEKDAY(
       E$2,
       2)<6)

Solving the challenge of Condition Formatting with Python

Python solution 1 for Condition Formatting, proposed by Konrad Gryczan, PhD: 
path = "files/Excel Challenge 18th August.xlsx"
input = pd.read_excel(path, usecols = 'B:D', skiprows=1, nrows = 4)
def generate_dates(start_date, n):
 dates = pd.date_range(start=start_date, periods=n*2, freq='D')
 return dates
result = input.assign(dates=input.apply(lambda row: generate_dates(row['Start'], row['Days']), axis=1)) 
 .explode('dates') 
 .set_index('dates') 
 .drop(columns=['Start']) 
 .reset_index() 
 .assign(wday=lambda df: np.where(df['dates'].dt.dayofweek.isin([5, 6]), '', 'X'),
 wday_lab=lambda df: df['dates'].dt.dayofweek,
 dates=lambda df: df['dates'].dt.strftime('%m-%d'),
 nrow=lambda df: df.groupby(['Project', 'wday']).cumcount() + 1)
 .assign(wday = lambda df: np.where((df['wday_lab'] == 5) | (df['wday_lab'] == 6) | (df["nrow"] > df['Days']),'', 'X')) 
 .pivot(index='Project', columns='dates', values='wday') 
 .fillna('')
result = result.iloc[:, :-5]
print(result)

Solving the challenge of Condition Formatting with R

R solution 1 for Condition Formatting, proposed by Konrad Gryczan, PhD: 
library(tidyverse)
library(readxl)
path = "files/Excel Challenge 18th August.xlsx"
input = read_excel(path, range = "B2:D6")
generate_dates <- Vectorize(function(start_date, n) {
 dates <- seq.Date(from = as.Date(start_date), 
 by = "day", 
 length.out = n * 2)
})
result = input %>%
 mutate(dates = generate_dates(Start, Days)) %>%
 unnest(dates) %>%
 complete(dates) %>%
 mutate(wday = ifelse(wday(dates) %in% c(1,7), "", "X"),
 dates = str_sub(as.character(dates), 6, 10)) %>%
 mutate(nrow = cumsum(wday == "X"),
 wday = ifelse(nrow > Days, "", wday), 
 .by = Project) %>%
 select(Project, dates, wday) %>%
 pivot_wider(names_from = dates, values_from = wday, values_fill = list(wday = "")) %>%
 select(1:14)

Leave a Reply