Date & Time
Extract date or time from a timestamp: split serial numbers with INT and MOD
Strip the clock fraction from date-time timestamps with INT(timestamp) so date lookups and daily groupings match cleanly.
Why 2026-04-15 15:00 does NOT equal 2026-04-15
A timestamp combines an integer calendar day before the decimal point with a clock fraction after it: April 15, 2026 at 15:00 is stored as 46127.625, whereas midnight April 15, 2026 is 46127.0.
Even if you format 46127.625 to display only "2026-04-15", VLOOKUP and exact = checks still compare 46127.625 against 46127 and return FALSE/#N/A! Calling INT(A2) strips the decimal fraction to leave pure integer date 46127, and MOD(A2, 1) isolates the 0.625 clock fraction.
| Timestamp (A) | Raw = Date Check | INT(A) Date Serial | MOD(A, 1)*24 Hours |
|---|---|---|---|
| 2026-04-15 15:00 (46127.625) | FALSE | 46127 | 15 |
| 2026-04-15 00:00 (46127.000) | TRUE | 46127 | 0 |
Extract integer date with INT and clock fraction with MOD
INT(A2) = B2 evaluates TRUE (both equal 46127), and MOD(A2, 1) * 24 extracts 15.00 hours.
=INT(A2)Isolate the time-of-day portion
Subtracting INT(A2) (or taking MOD(A2, 1)) removes the calendar date while keeping hours, minutes, and seconds intact.
=ROUND(MOD(A2, 1) * 24, 2)Try the example
Use the original values or change them to test your understanding.