Crispo - Excel Challenge 33 2024

excel-challenges
weekly-exercises
Easy Sunday Excel Challenge
Published

August 18, 2024

Illustration for Crispo - Excel Challenge 33 2024

Challenge Description

Easy Sunday Excel Challenge

⭐ ⭐e.g Proj A starts 16th for 5 days but ends on 22nd since weekend is not counted

Solutions

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)

# # A tibble: 4 × 14
#   Project `08-16` `08-17` `08-18` `08-19` `08-20` `08-21` `08-22` `08-23` `08-24` `08-25` `08-26` `08-27` `08-28`
#   <chr>   <chr>   <chr>   <chr>   <chr>   <chr>   <chr>   <chr>   <chr>   <chr>   <chr>   <chr>   <chr>   <chr>  
# 1 A       "X"     ""      ""      "X"     "X"     "X"     X       ""      ""      ""      ""      ""      ""     
# 2 D       "X"     ""      ""      "X"     "X"     "X"     X       "X"     ""      ""      "X"     "X"     "X"    
# 3 B       ""      ""      ""      ""      "X"     "X"     X       "X"     ""      ""      "X"     ""      ""     
# 4 C       ""      ""      ""      ""      ""      ""      X       "X"     ""      ""      "X"     ""      ""

  • Logic:

    • Reads the workbook range needed for the challenge

    • Reshapes the data to the grain required by the task

    • Builds the intermediate helper columns that drive the final answer

    • Uses direct text-pattern extraction instead of manual cleanup

  • Strengths:

    • The R solution stays compact and mirrors the workbook logic closely.

  • Areas for Improvement:

    • The code assumes the workbook layout and named ranges remain stable.

  • Gem:

    • The best part of the solution is choosing a tidy intermediate shape before producing the final answer.
import pandas as pd
import numpy as np

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)

# dates   08-16 08-17 08-18 08-19 08-20 08-21 08-22 08-23 08-24 08-25 08-26 08-27 08-28
# Project
# A           X                 X     X     X     X
# B                                   X     X     X     X                 X
# C                                               X     X                 X
# D           X                 X     X     X     X     X                 X     X     X

  • Logic:

    • Reads the workbook range needed for the challenge

    • Reshapes the data to the grain required by the task

    • Aggregates or ranks values at the correct grouping level

    • Builds the intermediate helper columns that drive the final answer

  • Strengths:

    • The Python version keeps the same rule in a direct pandas-oriented workflow.

  • Areas for Improvement:

    • As with the R version, any workbook layout change would require small adjustments.

  • Gem:

    • The implementation stays close to the stated challenge instead of adding unnecessary complexity.

Difficulty Level

This task is moderate:

  • It combines familiar Excel-style logic with at least one non-trivial reshape, grouping, or parsing step.

  • The answer depends on getting the output layout exactly right.