CODEWITHSUNDEEP
excelexcel-formula
Kishan_KP
Kishan_KP

Reputation: 4518

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

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?

Upvotes: 9

Views: 114273

Answers (3)

Pompa
Pompa

Reputation: 1

For example, type .633 in B2 cell. When we convert 0.633 to time means we convert 0.633day. We consider 24 hours as 1 day.

Follow the steps:

  1. Select B2 cell and right click.
  2. From shortcut menu, go to Format cells. Format cells dialog box appears.
  3. In the dialog box. click custom under Category list. Find hh:mm:ss from right list.
  4. Now edit it as hhmmss in the right side just under 'Type:'.

You may read this tutorial from msofficeworld: Format Time in Excel

Upvotes: -1

K_B
K_B

Reputation: 3678

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.

Upvotes: 11

Aditya
Aditya

Reputation: 377

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.

Upvotes: 2

Related Questions