Excel BI - Excel Challenge 926

🔰 Normalize mixed duration strings and reformat them into a padded dd.hh:mm:ss representation.

Illustration for Excel BI - Excel Challenge 926

Challenge Description

🔰 The data contains days, hours, minutes, and seconds qualified by d, h, m, and s. Convert each value to the standardized dd.hh:mm:ss format.

Solutions

R Solution

library(tidyverse)
library(readxl)

path <- "900-999/926/926 Conversion to ddhhmmss Format.xlsx"
input <- read_excel(path, range = "A1:A23")
test <- read_excel(path, range = "B1:B23")

parse_time <- function(x) {
  str_match(x, "(?:(\\d+)d)?(?:(\\d+)h)?(?:(\\d+)m)?(?:(\\d+)s)?") |>
    as_tibble(.name_repair = "minimal") |>
    set_names(c("all", "d", "h", "m", "s")) |>
    select(-all) |>
    mutate(across(everything(), ~ replace_na(as.numeric(.x), 0))) |>
    transmute(total = d * 86400 + h * 3600 + m * 60 + s) |>
    mutate(
      sprintf(
        "%02d.%02d:%02d:%02d",
        total %/% 86400,
        (total %% 86400) %/% 3600,
        (total %% 3600) %/% 60,
        total %% 60
      )
    ) |>
    pull()
}

result <- input %>%
  mutate(parsed = map_chr(Data, parse_time))

all.equal(result$parsed, test$`Answer Expected`)
# [1] TRUE

R Analysis

• Logic: Parse optional unit components, convert everything to total seconds, then rebuild the padded day-hour-minute-second string.
• Strengths: The intermediate total-seconds representation makes the final formatting deterministic and easy to trust.
• Areas for Improvement: The regex is compact but dense, so it helps to read it as four optional captures rather than one long pattern.
• Gem: Normalizing to one neutral unit first makes messy mixed-duration inputs easy to format consistently.

Python Solution

import pandas as pd
import re

path = "900-999/926/926 Conversion to ddhhmmss Format.xlsx"
input = pd.read_excel(path, usecols="A", nrows=23)
test = pd.read_excel(path, usecols="B", nrows=23)

def parse_time(x):
    t = pd.to_timedelta(re.sub(
        r'(\d+)([dhms])',
        lambda m: f"{m.group(1)} {'days hours minutes seconds'.split()['dhms'.index(m.group(2))]}",
        x
    ))
    d = t.days
    h = t.seconds // 3600
    m = (t.seconds % 3600) // 60
    s = t.seconds % 60
    return f"{d:02}.{h:02}:{m:02}:{s:02}"

result = input["Data"].apply(parse_time)

print(result.equals(test["Answer Expected"]))
# True

Python

The Python version is especially concise because it rewrites shorthand units into words that to_timedelta() already understands. That delegates most of the parsing work to pandas and keeps the rest focused on formatting.

Difficulty Level

Medium

The output format is simple, but the mixed optional units make the normalization step the core of the challenge.

Related Challenges

• Excel Challenge 930
• Excel Challenge 918
• Excel Challenge 914