Day 103 · Saturday, 14 November 2026

From the archive

Intermediate

Pay Overnight Shifts Correctly with MOD on Clock Times

Subtracting a clock-in time from a clock-out time gives the length of a shift, until the shift runs past midnight and the answer turns negative. Excel stores a time as a fraction of a day, so MOD with a divisor of 1 wraps that negative gap back round the clock into the hours actually worked.

Task:

You run payroll for a care home that staffs around the clock. Last Saturday's timesheet is in A2:D7: when each person clocked in, when they clocked out, and how many minutes of unpaid break they took. Three of the shifts finished the next morning. In E2, work out the paid hours for the shift as a decimal number of hours, so 8 hours 15 minutes reads 8.25, with the unpaid break taken off. Use one formula that is right for day shifts and overnight shifts alike, and copy it down to E7.

The daily challenge is part of Pro

A new exercise every morning, plus every day that came before it — a back catalogue that grows by one a day, whether or not you were here for it.

Every day so far

August 2026

September 2026

October 2026

November 2026

18
SolvedOpenProLatest

Recent days

The last 14 days. Today's challenge is free while your streak is alive; Pro opens every past day too.