Date & Time
Parse 4-digit military time ("0930", 1745, 15) into real Excel time and hours
Convert numeric HHMM codes or numeric text to clock labels and elapsed hours for intervals shorter than one day.
Why 1745 - 930 = 815 instead of 8.25 hours—and how INT + MOD fixes it
Mainframe, hospital scheduling, and aviation exports frequently store clock times as compact integers without colons: 930 for 09:30, 1745 for 17:45, or 15 for 00:15 (12:15 AM). Subtracting raw integers gives base-100 garbage (1745 - 930 = 815)!
Integer-dividing by 100 via INT(VALUE(A2) / 100) isolates the 24-hour clock hour—even when leading zeros were stripped (930 -> 9, 15 -> 0)—and MOD(VALUE(A2), 100) extracts the exact minutes (0–59) for TIME(hr, min, 0)!
| Raw HHMM Input | INT(n / 100) Hours | MOD(n, 100) Minutes | Formatted 24h & Decimal Hours |
|---|---|---|---|
| 930 (or "0930") | 9 | 30 | "09:30" (9.50 hrs from midnight) |
| 1745 | 17 | 45 | "17:45" (8.25 hrs shift from 09:30) |
| 15 (or "0015") | 0 | 15 | "00:15" (15 mins past midnight) |
Convert 1-to-4 digit military input into formatted HH:MM text
Handles numeric 930, text "0930", and post-midnight 15 ("00:15") identically.
The output is formatted text. TIME itself returns a numeric fraction of a day. Numeric conversion is intentionally used here rather than a strict digit-only parser; invalid hours/minutes are rejected instead of normalized.
=IFERROR(IF(COUNTBLANK(A2)>0,"Invalid time",LET(code,VALUE(A2),IF(OR(code<0,code>2359,code<>INT(code),MOD(code,100)>59),"Invalid time",RIGHT("00"&INT(code/100),2)&":"&RIGHT("00"&MOD(code,100),2)))),"Invalid time")Elapsed clock hours for an interval shorter than 24 hours
An end time earlier than the start is treated as the next day. Equal times mean zero hours, not 24. Clock codes carry no date or timezone, so this cannot measure multi-day or daylight-saving transitions.
=IFERROR(IF(COUNTBLANK(A2:B2)>0,"Invalid time",LET(startCode,VALUE(A2),endCode,VALUE(B2),IF(OR(OR(startCode<0,startCode>2359,startCode<>INT(startCode),MOD(startCode,100)>59),OR(endCode<0,endCode>2359,endCode<>INT(endCode),MOD(endCode,100)>59)),"Invalid time",LET(startTime,TIME(INT(startCode/100),MOD(startCode,100),0),endTime,TIME(INT(endCode/100),MOD(endCode,100),0),ROUND(MOD(endTime-startTime,1)*24,2))))),"Invalid time")Try the example
Use the original values or change them to test your understanding.