digest
Short Summary
Main recommendation: Use the TEXT function with custom number formats to convert between real Excel time values and HHMM text, which is the video's answer for both directions of the conversion. Time values are stored as decimal proportions of a 24-hour day, so the conversion requires a specific formatting code for each direction, shown in the steps below.
Key Points
- Converting a time value to HHMM text is a one-step job for the TEXT function, using a custom number format.
- Converting HHMM text back to a time value is trickier, because the text is not a valid time until it is changed into a number.
- The reverse conversion works by first formatting the text with a colon inserted, then adding zero to coerce it into a number, then applying a Time format.
- The backslash character in a custom number format tells Excel to display the next character literally, which is how the colon is inserted.
Details Worth Noting
- Concrete instructions:
- Time value to HHMM text: enter =TEXT(value,"hhmm") in the cell next to the time value, then press Control+Enter and fill down. In the format, "hh" gives a leading zero for the hour, and "mm" gives minutes.
- HHMM text to time value: enter =TEXT(value,"00:00") to insert the colon, press Control+Enter, then press F2 and add +0 to the end of the formula to convert the text to a number, and press Control+Enter again.
- Then, with the result selected, choose Time from the number format options so the value displays as a time, and fill down.
- The example converts 0645 (6 hours 45 minutes) to a time value equal to 6 hours 45 minutes divided by 24.
- The "0" digit placeholder in the TEXT format forces leading zeros, so single-digit hours still show two digits.
Bottom Line
Text-to-time and time-to-text conversions both depend on Excel's custom number formats, and the key trick is using the backslash to insert the colon when converting from HHMM text.
