Convert time to number

[bassls]

I have a column formatted as time that has time stamps in it, like 22:20. I want to convert them to numbers without the colon, like 2220. Thanks.

 

[Aladin Akyurek]

I have a column formatted as time that has time stamps in it, like 22:20. I want to convert them to numbers without the colon, like 2220. Thanks.

Something like...

=TEXT(HOUR(A2),"00")&TEXT(MINUTE(A2),"00")

 

[bassls]

That formula converts everything to 0:00. I want a number without the colon.

 

[Aladin Akyurek]

That formula converts everything to 0:00. I want a number without the colon.

Did you try it? This is what I get:

<TABLE style="WIDTH: 118pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=158><COLGROUP><COL style="WIDTH: 60pt; mso-width-source: userset; mso-width-alt: 2844" width=80><COL style="WIDTH: 58pt; mso-width-source: userset; mso-width-alt: 2759" width=78><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 60pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 width=80> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 58pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=78> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19 align=right>22:20</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>2220</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19 align=right>8:06</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>0806</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19 align=right>18:45</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>1845</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD></TR></TBODY></TABLE>

 

[shg]

Another way:

=100*DOLLARFR(A1*24, 60)

Format as 0000

 

[bassls]

Still not sure what I was doing wrong, but I was able to get it to work. Thanks!

 

[Aladin Akyurek]

Still not sure what I was doing wrong, but I was able to get it to work. Thanks!

Great. Thanks for providing feedback. Invoking DOLLARFR is also a good idea.

 

[T. Valko]

I have a column formatted as time that has time stamps in it, like 22:20. I want to convert them to numbers without the colon, like 2220. Thanks.

Here's another one...

=--SUBSTITUTE(TEXT(A2,"h:mm"),":","")

That returns a numeric NUMBER.

The suggestion in post #2 returns a TEXT string.

 

[Scott Huish]

=TEXT(A1,"hhmm")+0
Format as 0000

Excel Workbook
	A	B
1	22:20	2220
2	1:39	0139
Sheet2