如何在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?

我有一个excel表格，其中一列是事务时间，它的格式是HH:MM:SS。但是我需要用HHMM格式的事务时间，这样我就可以将excel表格上传到我的应用程序中。到目前为止，我正在手动格式化每一行，有什么方法可以同时对整个列应用所需的格式吗?

3 个解决方案

#1

If for your eyes only:

如果只为你的眼睛:

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

选择整列，右键单击选定的某个位置，进入格式单元格、制表符、Catergory自定义。

Type HHmm

类型HHmm

Press OK

按OK

done.

完成了。

If you need this as the actual value:

如果你需要这个作为实际值:

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

使列包含以下公式=TEXT( ;"HHmm")

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

注意，是否需要a或;在这个公式。

#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.
在另一个单元格中使用函数。假设单元格A1包含文本15:48:58。在另一个单元格中使用公式(如B1) as =TEXT(HOUR(A1)，“00”)&TEXT(MINUTE(A1)，“00”)。输出是1548。

#3

-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.

例如，在B2单元中输入.633。当我们把0。633转换成时间，意味着我们把0。633天转换成时间。我们认为24小时为1天。

Follow the steps:

遵循的步骤:

Select B2 cell and right click.
选择B2单元格并右击。
From shortcut menu, go to Format cells. Format cells dialog box appears.
从快捷菜单，进入格式化单元格。显示“格式单元格”对话框。
In the dialog box. click custom under Category list. Find hh:mm:ss from right list.
在对话框中。单击类别列表下的自定义。从右边列表中找到hh:mm:ss。
Now edit it as hhmmss in the right side just under 'Type:'.
现在将它编辑为hhmmss，在右边的“Type:”下。

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

您可以阅读msofficeworld:在Excel中格式化时间

#1