Date & Time
HOUR and MINUTE: extract clock components and calculate elapsed shift minutes
Pull 0-23 hours and 0-59 minutes from timestamps with HOUR and MINUTE, and avoid the 24-hour rollover trap on long durations.
Decompose timestamps into hour (0-23) and minute (0-59)
HOUR(serial_number) returns the integer hour from 0 (12:00 AM) to 23 (11:00 PM), and MINUTE(serial_number) returns the minute from 0 to 59—ignoring any whole date serial before the decimal point.
For a same-day shift from 08:30 to 17:15, converting both to minutes gives (17*60 + 15) - (8*60 + 30) = 1035 - 510 = 525 elapsed minutes.
| ShiftStart (A) | ShiftEnd (B) | Start Hour:Min | End Hour:Min | Elapsed Min |
|---|---|---|---|---|
| =TIME(8, 30, 0) | =TIME(17, 15, 0) | 8:30 | 17:15 | 525 |
Calculate same-day shift minutes from HOUR and MINUTE
Multiplying HOUR by 60 and adding MINUTE converts each clock stamp into minutes since midnight.
=(HOUR(B2)*60 + MINUTE(B2)) - (HOUR(A2)*60 + MINUTE(A2))Warning: durations >= 24 hours wrap in HOUR()
A 30-hour cumulative duration equals 1.25 days. HOUR(1.25) extracts only the clock remainder (6), whereas INT(1.25 * 24) preserves all 30 total hours.
=HOUR(1.25)Try the example
Use the original values or change them to test your understanding.