Excel BI - Excel Challenge 639

excel-challenges

excel-formulas

🔰 Find the total time in hours worked on each day.

Published

March 24, 2026

Illustration for Excel BI - Excel Challenge 639

Challenge Description

🔰 Find the total time in hours worked on each day. Time is tabulated in hhmm format (24 hours format).

Solutions

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

path = "Excel/639 Total Hours Per Day.xlsx"
input = read_excel(path, range = "A1:H6")
test  = read_excel(path, range = "A9:B16")

result = input %>%
  pivot_longer(cols = -Name, names_to = "Day", values_to = "Hours") %>%
  separate(Hours, into = c('from', 'to'), sep = '-') %>%
  na.omit() %>%
  mutate(across(c(from, to), ~ parse_date_time(paste0(substr(., 1, 2), ":", substr(., 3, 4)), orders = "HM"))) %>%
  mutate(hours = as.numeric(difftime(to, from, units = "hours"))) %>%
  summarise(`Total Hours` = sum(hours), .by = Day)

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

Logic: Read the workbook ranges needed for the challenge; Derive the required intermediate columns; Parse the packed text or string structure; Aggregate or rank the data at the required grouping level.
Strengths: The reshaping step mirrors the workbook output closely instead of forcing extra post-processing.
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 last reshape turns a raw transformation into something that already looks like a report.

import pandas as pd
from datetime import datetime

path = "639 Total Hours Per Day.xlsx"
input = pd.read_excel(path,  usecols="A:H", nrows=6)
test = pd.read_excel(path,  usecols="A:B", skiprows=8, nrows=8)

input_long = input.melt(id_vars=['Name'], var_name='Day', value_name='Hours').dropna()
input_long[['from', 'to']] = input_long.pop('Hours').str.split('-', expand=True)
input_long[['from', 'to']] = input_long[['from', 'to']].apply(lambda x: pd.to_datetime(x.str[:2] + ':' + x.str[2:], format='%H:%M'))
input_long['hours'] = (input_long['to'] - input_long['from']).dt.total_seconds() / 3600
result = input_long.groupby('Day').agg({'hours': 'sum'}).reset_index()
result.columns = ['Day', 'Total Hours']
 
print(sorted(result)==(sorted(test))) # True

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

Difficulty Level

Medium

The individual steps are manageable, but the correct transformation pattern is not obvious from the raw data.