Dates & Time
Add Decimal Minutes or Decimal Hours to a Clock Time (Midnight-Safe MOD)
Add or subtract decimal minutes (C2 / 1440) or decimal hours (C2 / 24) to any 24-hour clock time—wrapping cleanly across midnight without negative-time #### errors via MOD(..., 1).
Convert decimal minutes or hours into fractional days before adding to clock times!
Spreadsheets store clock times as fractions of a 24-hour day (1.0 = 24 hours = 1,440 minutes = 86,400 seconds): 12:00 noon is 0.50 and 23:30 is 0.979167! Adding a raw number like +75 minutes directly to 23:30 adds 75 WHOLE DAYS instead of 75 minutes, while TIME(0, 75, 0) silently truncates fractional decimal minutes (such as 12.5 minutes) AND overflows past 32,767!
Divide decimal minutes by 1440 (24 * 60) or decimal hours by 24, add to the start time, and wrap the sum inside MOD(..., 1)! MOD(B2 + (C2 / 1440), 1) strips integer day carryovers both forward past 24:00 (23:30 + 75 min -> 00:45) and backward before 00:00 (00:15 - 30 min -> 23:45)—eliminating negative-time #### errors completely!
| Start Clock Time (B2) | Offset to Add | Day-Fraction Formula | Wrapped 24h Result |
|---|---|---|---|
| 23:30 (Night Shift) | +75 decimal minutes | MOD(B2 + (75 / 1440), 1) | 00:45 (0.75 decimal hr) |
| 08:15 (Morning Dispatch) | +2.5 decimal hours (150 min) | MOD(B2 + (2.5 / 24), 1) | 10:45 (10.75 decimal hr) |
| 00:15 (Rollback Check) | -30 decimal minutes | MOD(B2 + (-30 / 1440), 1) | 23:45 (Wraps backward!) |
Add Decimal Minutes to Clock Time & Format as 24-Hour HH:MM
Divides decimal minutes C2 by 1,440, wraps across midnight with MOD(..., 1), and formats zero-padded HH:MM via HOUR and MINUTE.
=RIGHT("00" & HOUR(MOD(B2 + (C2 / 1440), 1)), 2) & ":" & RIGHT("00" & MINUTE(MOD(B2 + (C2 / 1440), 1)), 2)Add Decimal Hours to Clock Time (Returned as Decimal Hour of Day 0–24)
Adds D2 / 24 hours to B3, wraps across midnight via MOD(..., 1), and multiplies by 24 for decimal clock hour.
=ROUND(MOD(B3 + (D3 / 24), 1) * 24, 2)Try the example
Use the original values or change them to test your understanding.