how to format time from HH:MM:SS to HHMM in excel?

Question

I have an excel sheet in which one of the column is transaction time and its format is HH:MM:SS. But I need transaction time in HHMM format so that i can upload that excel sheet into my application. As of now i am manually formatting each row, is there any way i can apply required format to whole column at once?

Answer 1

If for your eyes only:

select whole column, right click somewhere in your selection, go to Format Cells, tab Number, Catergory Custom.

Type HHmm

Press OK

done.

If you need this as the actual value:

make a column containing the following formula =TEXT(<targetcell>;"HHmm")

Note that it is language-dependant whether you need a , or ; in that formula.

Answer 2

You have two options:

• Custom format the field.
• Use a function in another cell. Suppose, cell A1 contains the text 15:48:58. You use the formula in another cell (say B1) as =TEXT(HOUR(A1),"00")&TEXT(MINUTE(A1),"00"). This gives the output as 1548.