Converting military times to numbers

[Wire323]

I have a column that contains times in military format. I want to convert the times to numbers, with no formatting.

For example:

Right now the columns show the following:

0:01
0:23
2:14
13:32
19:54

Their actual values are:

12:01:00 AM
12:23:00 AM
2:14:00 AM
1:32:00 PM
7:54:00 PM

I want it to instead have the following values:

1
23
214
1332
1954

They should be formatted as numbers, with no indication that they represent times.

Is this possible?

I have a worksheet that contains military times in number format that I need to compare against.

Thanks.

[geekgirlau]

Assuming your time is in cell A1,

=VALUE(HOUR(A1)&(B1*60*24)-(HOUR(A1)*60))

[Chente28]

geekgirlau,
B1 should be A1 on your formula. :)
It works perfectly.

this also worked for me. Assuming A1 has the time:

=CONCATENATE(HOUR(A1),MINUTE(A1))

But i get a 01 instead of 1.

:dunno

[jindon]

=--CONCATENATE(HOUR(A1),MINUTE(A1))
will convert the text value to number...

[TonyJollans]

Just answered this on Tek-Tips - there's a FAQ somewhere on cross-posting but off the top of my head I don't where it is - basically, it's not actaully wrong but it's good manners, to say the least, to state that you are doing it.

I don't think any of the answers here are correct but I think this will do it:

=VALUE(TEXT(A1,"HHMM"))

[Wire323]

Sorry TonyJollans. I wasn't aware of the cross-posting etiquette, but it definitely makes sense. I'll be sure to state that I am doing so in the future. I was panicking because I need to have something done for work tomorrow and I just couldn't figure this little part out.

Thanks.

[TonyJollans]

No problem.

And Welcome to VBAX!