Crispo - Excel Challenge 18 2025

excel-challenges
weekly-exercises
Easy Sunday Excel Challenge
Published

May 4, 2025

Illustration for Crispo - Excel Challenge 18 2025

Challenge Description

Easy Sunday Excel Challenge

⭐ Problem Solution Next LEAP Years Start Period Days

Solutions

library(tidyverse)
library(readxl)
library(lubridate)

Sys.setlocale("LC_ALL", "English")

path = "files/2025-05-04/Challenge21.xlsx"
test = read_excel(path, range = "E3:F19")
no_periods = 2
start_date = as.Date("2025-01-07")

leap_years = c()
while (length(leap_years) < no_periods) {
  if (leap_year(start_date)) {
    leap_years = c(leap_years, year(start_date))
  }
  start_date = start_date + years(1)
}

first = make_date(year = leap_years[1], month = 1:12, day = 1) %>%
  as_tibble() %>%
  mutate(Days = days_in_month(value), Period = format(value, "%b%y")) %>%
  select(Period, Days)

second = make_date(year = leap_years[2], month = 1:12, day = 1) %>%
  as_tibble() %>%
  mutate(
    Days = days_in_month(value),
    Period = paste0("Q", quarter(value), "_", year(value))
  ) %>%
  summarise(Days = sum(Days), .by = Period)

result = bind_rows(first, second)

all.equal(result, test, check.attributes = FALSE)
#> [1] TRUE

  • Logic:

    • Reads the workbook range needed for the challenge

    • Aggregates or ranks values at the correct grouping level

    • Builds the intermediate helper columns that drive the final answer

    • Applies the rule iteratively until the output is complete

  • 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
from datetime import datetime, timedelta
from dateutil.relativedelta import relativedelta
from calendar import monthrange, isleap

path = "files/2025-05-04/Challenge21.xlsx"
test = pd.read_excel(path, usecols="E:F", skiprows=2, nrows=17)

no_periods = 2
start_date = datetime.strptime("2025-01-07", "%Y-%m-%d")

leap_years = []
while len(leap_years) < no_periods:
    if isleap(start_date.year):
        leap_years.append(start_date.year)
    start_date += relativedelta(years=1)

first_dates = [datetime(year=leap_years[0], month=m, day=1) for m in range(1, 13)]
first = pd.DataFrame({
    "Period": [date.strftime("%b%y") for date in first_dates],
    "Days": [monthrange(date.year, date.month)[1] for date in first_dates]
})

second_dates = [datetime(year=leap_years[1], month=m, day=1) for m in range(1, 13)]
second = pd.DataFrame({
    "Period": [f"Q{((date.month - 1) // 3) + 1}_{date.year}" for date in second_dates],
    "Days": [monthrange(date.year, date.month)[1] for date in second_dates]
})
second = second.groupby("Period", as_index=False).sum()

result = pd.concat([first, second], ignore_index=True)

print(result)

  • Logic:

    • Reads the workbook range needed for the challenge

    • Aggregates or ranks values at the correct grouping level

    • Applies the rule iteratively until the output is complete

  • 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 easy to moderate:

  • The business rule is readable, but the workbook still needs a few careful transformation steps.