Excel Magic Trick 1262: Convert Times Values to HHMM Text or Text HHMM Values to Time Values

13
41




About this tutorial:

Video duration: 3:48
Download File: http://people.highline.edu/mgirvin/excelisfun.htm
Learn how to use the TEXT function and custom Number Formatting to:
1. (00:12) Convert Times Values to HHMM Text with TEXT function and Custom Number Formatting “hhmm”
2. (01:29) Convert Text HHMM Values to Time Values with TEXT function and Custom Number Formatting “00:00”. Learn how to use the back slash character to insert a single character into a Number Formatting.

13 COMMENTS

  1. TEXT function also good in Month and Year, especially for creating accounting period.
    Text(Month,"yyyyMM") for account period start from Jan or Text(EDATE(Month,6),"yyyyMM") if period start from Jul

  2. Brilliant trick 🙂 Thanks.
    One small note – this is excel language sensitive. So if you want to copy this formuła in polish excel, you get an error (not "0645" but "hh01").
    Below, an example of language independent formula:
    =TEXT(HOUR(A2),"00")&TEXT(MINUTE(A2),"00")
    Of course this is not as elegant as your solution :-(

LEAVE A REPLY