Omid - Challenge 166

data-challenges
advanced-exercises
🔰 In the provided table, the Date Time values correspond to different time zones (specified in the column GMT From).
Published

March 24, 2026

Illustration for Omid - Challenge 166

Challenge Description

🔰 In the provided table, the Date Time values correspond to different time zones (specified in the column GMT From).

Solutions

library(tidyverse)
library(readxl)

path = "files/CH-166 Time Zone.xlsx"
input = read_excel(path, range = "B2:D12")
test  = read_excel(path, range = "F2:I12")

result = input %>%
  separate(`Date Time`, into = c("Day", "Month", "Year", "Hour", "Minute"), sep = " |:|/", convert = TRUE) %>%
  mutate(Day = Day + if_else(Hour >= 24, 1, 0),
         Hour = Hour %% 24) %>%
  mutate(`Date Time` = make_datetime(year = Year, month = Month, day = Day, hour = Hour, min = Minute),  .keep = "unused") %>%
  mutate(`GMT From` = str_remove(`GMT From`, "GMT") %>% as.numeric(),
         `GMT To` = str_remove(`GMT To`, "GMT") %>% as.numeric()) %>%
  mutate(`New Date Time` = `Date Time` + hours(`GMT To` - `GMT From`)) %>%
  select(`New Date Time`) %>%
  mutate(`New Date Time` = format(`New Date Time`, "%d/%m/%Y %H:%M"))
  

all.equal(result$`New Date Time`, test$`New Date Time`, check.attributes = FALSE)
# False, one discrepancy on 5th row.

  • Logic:

    • Reads the workbook ranges needed for the challenge

    • Builds the intermediate columns that drive the final result

    • Parses the text patterns directly instead of relying on manual cleanup

  • Strengths:

    • The R solution stays close to the workbook rule and keeps the transformation compact.

  • Areas for Improvement:

    • The code assumes the sheet structure and source ranges remain stable.

  • Gem:

    • The strongest part of the solution is choosing the right intermediate representation before shaping the final output.
import pandas as pd

path = "CH-166 Time Zone.xlsx"
input = pd.read_excel(path, usecols="B:D", skiprows=1, nrows=10)
test = pd.read_excel(path, usecols="I:I", skiprows=1, nrows=10)

input[['Day', 'Month', 'Year', 'Hour', 'Minute']] = input['Date Time'].str.extract(r'(\d+)/(\d+)/(\d+) (\d+):(\d+)').astype(int)
input['Day'] += (input['Hour'] >= 24).astype(int)
input['Hour'] %= 24
input['Date Time'] = pd.to_datetime(input[['Year', 'Month', 'Day', 'Hour', 'Minute']])
input['GMT From'] = input['GMT From'].str.replace('GMT', '').astype(int)
input['GMT To'] = input['GMT To'].str.replace('GMT', '').astype(int)
input['New Date Time'] = input['Date Time'] + pd.to_timedelta(input['GMT To'] - input['GMT From'], unit='h')
input['New Date Time'] = input['New Date Time'].dt.strftime('%d/%m/%Y %H:%M')
result = input[['New Date Time']]

print(result == test) # False, Discrepancy on one row. Index 4.

  • Logic:

    • Reads the workbook ranges needed for the challenge

  • Strengths:

    • The Python version follows the same rule in a direct dataframe-oriented implementation.

  • Areas for Improvement:

    • The code assumes the workbook layout remains stable, so any sheet redesign would require small adjustments.

  • Gem:

    • The implementation stays close to the original workbook rule instead of adding unnecessary abstraction.

Difficulty Level

This task is moderate:

  • The core logic is clear, but the correct transformation pattern is not obvious from the raw input.

  • The challenge combines multiple reshaping, grouping, or parsing steps.