Date & Time
Convert Compact HHMMSS Timestamps (91500 or 144530) Into Time & Duration
Parse colon-free 6-digit HHMMSS log integers—even when CSV import strips leading zeros from 091500 to 91500—using TIME(INT(x/10000), MOD(INT(x/100),100), MOD(x,100)).
Why LEFT/MID/RIGHT breaks when CSV imports strip "091500" down to 91500—and INT + MOD never fails!
Mainframe badge readers, aviation ACARS logs, and warehouse scanners export compact HHMMSS timestamps without colons ("091500" for 09:15:00, "144530" for 14:45:30). The moment you open that CSV, spreadsheet auto-typing strips leading zeros—turning "091500" into 5-digit integer 91500 and "000530" into 530—breaking naive LEFT(x, 2) string slicing completely!
Extracting hours, minutes, and seconds arithmetically via H = INT(x / 10000), M = MOD(INT(x / 100), 100), and S = MOD(x, 100) inside TIME(H, M, S) parses 530, 91500, and 144530 identically—while catching impossible clock digits like 96500 (minute 65 >= 60)!
| Start HHMMSS (B2) | End HHMMSS (B3) | Elapsed Hours (* 24) | Elapsed Seconds (* 86400) |
|---|---|---|---|
| 91500 (09:15:00) | 144530 (14:45:30) | 5.5083 hrs | 19,830 sec |
| 91500 (09:15:00) | 121500 (12:15:00) | 3.0000 hrs | 10,800 sec |
| 96500 (Minute 65!) | 144530 | "Invalid HHMMSS Integer" | Rejects M >= 60 or S >= 60 |
Elapsed Decimal Hours Between Two Compact HHMMSS Timestamps
Validates hours (0–23), minutes (0–59), and seconds (0–59) for both B2 and B3 before converting TIME(end) - TIME(start) into decimal hours (* 24).
=IF(OR(B2 < 0, B2 > 235959, B3 < 0, B3 > 235959, MOD(INT(B2 / 100), 100) >= 60, MOD(B2, 100) >= 60, MOD(INT(B3 / 100), 100) >= 60, MOD(B3, 100) >= 60), "Invalid HHMMSS Integer", ROUND((TIME(INT(B3 / 10000), MOD(INT(B3 / 100), 100), MOD(B3, 100)) - TIME(INT(B2 / 10000), MOD(INT(B2 / 100), 100), MOD(B2, 100))) * 24, 4))Elapsed Seconds Between Compact HHMMSS Timestamps
Multiplies serial time difference by 86,400 seconds/day.
=IF(OR(B2 < 0, B2 > 235959, B3 < 0, B3 > 235959, MOD(INT(B2 / 100), 100) >= 60, MOD(B2, 100) >= 60, MOD(INT(B3 / 100), 100) >= 60, MOD(B3, 100) >= 60), "Invalid HHMMSS Integer", ROUND((TIME(INT(B3 / 10000), MOD(INT(B3 / 100), 100), MOD(B3, 100)) - TIME(INT(B2 / 10000), MOD(INT(B2 / 100), 100), MOD(B2, 100))) * 86400, 0))Try the example
Use the original values or change them to test your understanding.