Excel BI - Excel Challenge 819

excel-challenges

excel-formulas

🔰 Answer Expected Emp No.

Published

March 24, 2026

Illustration for Excel BI - Excel Challenge 819

Challenge Description

🔰 Answer Expected Emp No. Shift Start Time End Time Shift1 Shift2 Shift3 Shift4 If an employee has worked continuously across shifts, then generate start and end time across the shifts for continuous shifts.

Solutions

library(tidyverse)
library(readxl)
library(hms)

path = "Excel/800-899/819/819 Merging Shifts.xlsx"
input = read_excel(path, range = "A2:D12")
test  = read_excel(path, range = "F2:H8")

merged_shifts = input %>%
  arrange(Emp_No = `Emp No.`, Shift_Num = parse_number(Shift)) %>%
  4%>%
  summarise(Start_Time = first(`Start Time`),
            End_Time   = last(`End Time`), .by = c(Emp_No, group_id)) %>%
  select(-group_id)

all.equal(result, test)
# [1] TRUE

Logic: Read the workbook ranges needed for the challenge; Aggregate or rank the data at the required grouping level.
Strengths: The code maps the workbook rule into a compact, reproducible pipeline.
Areas for Improvement: The solution assumes the workbook layout and selected ranges remain stable, so any structural change in the sheet would require small adjustments.
Gem: The elegant part is how little code is needed once the correct intermediate representation is chosen.

import pandas as pd

path = "800-899/819/819 Merging Shifts.xlsx"

input = pd.read_excel(path, usecols="A:D", skiprows=1, nrows=11)
test = pd.read_excel(path, usecols="F:H", skiprows=1, nrows=6).rename(columns=lambda x: x.replace('.1', ''))

input['Shift_Num'] = input['Shift'].str.extract(r'(\d+)').astype(int)
input = input.sort_values(['Emp No.', 'Shift_Num'])

input['group_id'] = (
    input.groupby('Emp No.').apply(
        lambda df: (df['Start Time'] != df['End Time'].shift()).cumsum()
    ).reset_index(level=0, drop=True)
)

input = input.groupby(['Emp No.', 'group_id']).agg({
    'Shift': 'first', 'Start Time': 'first', 'End Time': 'last'})
input = input.reset_index()
input = input.drop(columns=['group_id', 'Shift'])

print(input.equals(test)) # True

The Python version follows the same grouped logic and keeps the transformation explicit in a dataframe pipeline.

Difficulty Level

Easy / Medium

The business rule is clear, though the workbook still needs a few transformation steps to reach the expected output.