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

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.

[simonpender]

Thank you. I have been looking for this for years. This will make my life easier filling pay sheets.

[Vijay-]

Thanks a Ton, buddy! It saved our production.

[arbnsapper]

I spent an hour trying to figure this out; you are the man!!!! Thank you so

[PedroCabraldaCamara]

You gotta b kidding...back slash? Realy? Awesome! Tks a lot Mike

[karljolivet]

You are a source of so much information. Thank you for making these videos

[robertlohman]

Another slick trick from ExcelisFun. Love 'em

[CarlosMoreno-vwir]

Asesome advice!!! You just save my day!!! God Bless
Greetings from Monterrey, Mexico

[BillSzysz]

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 :-(

[Khadolkacha]

Thanks a ton..🙏🏾
Was looking for this in different places..Easy solution, perfect explanation
From 🇮🇳

[anthonyverdin]

Thanks Mike, you have saved my day again. I spent most of my day today looking for this solution. Your the best, Happy New Year!

[thomsontam]

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

[achyutsingh]

amazing trick. You saved a lot of my time

[dannyn]

Quick question: For example, if I am inputting 10:05 PM instead of AM how would I be able to do that without converting it into 24 hours clock form? Thank you in advance.

[MySpreadsheetLab]

thanks Mike. I didn't know about the \:

[thresaleecerda]

Thank you! How to get military time? I'll keep looking.

[Sal_A]

Don't know if I'll ever use \: but probably a good way to join other single characters too

[ExcelStrategy]

Formula solution always wins in flexibility !

[nuhawoley]

what about if i don't want to delete the old numbers and keep only time

[LiquidSnake]

Lets say I have C2=1 (as hour), D2=5 (as minutes), E2=0 (as seconds) and in F2=3900 as seconds.

In 3900 seconds is multiplied by 10% up to 90%

in A5=F2*0, B5=F2*0.1, C5=F2*0.2 up to J5=F2*0.9 which is 3500 but I wanted the results to be in time format which is 58:30.

[sespider]

Great.
But what if I want the Text format to LOOK like the time format?
I need to combine a text column with a time column. Bit it doesn't allow it.
For importing to a label file (Word), I need it to read as: Appt: 9:30am
Because Word doesn't have a time option in it's labeling wizard, I'm having to trick it by labeling the time column as "Address 2"
But I can't do this if I can't even combine a Text column with a time column, as needed.