Excel Data Analysis 简明教程

Working with Time Values

您从不同来源获取的数据可能包含时间值。在本章中，您将了解如何准备包含时间值的用于分析的数据。

The data that you obtain from different sources might contain time values. In this chapter, you will understand how to prepare your data that contains time values for analysis.

你将了解：

You will learn about −

  1. Time Formats Time in Serial FormatTime in Hour-Minute-Second Format

  2. Converting Times in Serial Format to Hour-Minute-Second Format

  3. Converting Times in Hour-Minute-Second Format to Serial Format

  4. Obtaining the Current Time

  5. Obtaining Time from Hour, Minute and Second

  6. Extracting Hour, Minute and Second from Time

  7. Number of hours between Start Time and End Time

Time Formats

Excel 支持 Time 值，有两种方式−

Excel supports Time Values in two ways −

  1. Serial Format

  2. In various Hour-Minute-Second Formats

您可以转换−

You can convert −

  1. Time in Serial Format to Time in Hour-Minute-Second Format

  2. Time in Hour-Minute-Second Format to Time in Serial Format

Time in Serial Format

以串行格式表示的 Time 是一个正数，表示 Time 是 24 小时天的一个分数，起点是午夜。例如，0.29 表示上午 7 点，0.5 表示中午 12 点。

Time in serial format is a positive number that represents the Time as a fraction of a 24-hour day, the starting point being midnight. For example, 0.29 represents 7 AM and 0.5 represents 12 PM.

你也可以在同一个单元格中组合 Date 和 Time 。序列号是指 1900 年 1 月 1 日之后的第几天，以及与给定时间相关的日期部分。例如，如果你输入 2016 年 5 月 17 日上午 6 点，当你将该单元格格式化为 General 时，它将转换为 42507.25。

You can also combine Date and Time in the same cell. The serial number is the number of days after January 1, 1900, and the time fraction associated with the given time. For example, if you type May 17, 2016 6 AM, it gets converted to 42507.25 when you format the cell as General.

Time in Hour-Minute-Second Format

Excel 允许你使用冒号 (:) 在小时后和秒前指定时间以小时-分钟-秒格式。例如，8:50 AM、8:50 PM 或仅使用 8:50 采用 12 小时制格式或作为 8:50、20:50 采用 24 小时制格式。时间 8:50:55 AM 表示 8 小时、50 分钟和 55 秒。

Excel allows you to specify time in Hour-Minute-Second Format with a colon (:) after the hour and another colon before the seconds. Example, 8:50 AM, 8:50 PM or just 8:50 using the 12-Hour Format or as 8:50, 20:50 in 24-Hour format. The time 8:50:55 AM represents 8 hours, 50 minutes and 55 seconds.

你也可以同时指定日期和时间。例如，如果你在单元格中输入 2016 年 5 月 17 日 7:25，它将显示为 5/17/2016 7:25，它表示 5/17/2016 7:25:00 AM。

You can also specify date and time together. For example, if you type May 17, 2016 7:25 in a cell, it will be displayed as 5/17/2016 7:25 and it represents 5/17/2016 7:25:00 AM.

Excel 支持基于你选择的 Locale （位置）的不同 Time 格式。因此，你需要首先确定手头 Time 格式和数据分析的兼容性。

Excel supports different Time formats based on the Locale (Location) you choose. Hence, you need to first determine the compatibility of your Time formats and data analysis at hand.

为了理解，你可以将美国视为语言环境。你找到以下 Time 格式可供 Date 和 Time 选择——2016 年 5 月 17 日下午 4 点 -

For understanding purpose, you can assume United States as the Locale. You find the following Time formats to choose for Date and Time – 17th May, 2016 4 PM −

  1. 4:00:00 PM

  2. 16:00

  3. 4:00 PM

  4. 16:00:00

  5. 5/17/16 4:00 PM

  6. 5/17/16 16:00

Converting Times in Serial Format to Hour-Minute-Second Format

要将串行时间格式转换为小时-分钟-秒格式，请按照以下步骤操作 -

To convert serial time format to hour-min-sec format follow the steps given below −

  1. Click the Number tab in the Format Cells dialog box

  2. Click Time under Category.

  3. Select the Locale. Available Time formats will be displayed as a list under Type.

  4. Click on a Format under Type to look at the Preview in the box adjacent to Sample.

converting times

在选择格式后，单击 OK

After choosing the Format, click OK

Converting Times in Hour-Minute-Second Format to Serial Format

您可以使用两种方式将 Hour-Minute-Second 格式中的时间转换为序列格式 −

You can convert Time in Hour-Minute-Second format to serial format in two ways −

  1. Using Format Cells dialog box

  2. Using Excel TIMEVALUE function

Using Format Cells dialog box

  1. Click the Number tab in the Format Cells dialog box.

  2. Click General under Category.

format cells dialog box

Using Excel TIMEVALUE Function

您可以使用 Excel TIMEVALUE 函数将 Time 转换为 Serial Number 格式。需要将 Time 参数用引号括起来。例如，

You can use Excel TIMEVALUE function to convert Time to Serial Number format. You need to enclose the Time argument in “”. For example,

TIMEVALUE ("16:55:15") 导致 0.70503472

TIMEVALUE ("16:55:15") results in 0.70503472

Obtaining the Current Time

如果需要根据当前时间执行计算，简单使用 Excel 函数 NOW ()。结果反映使用日期和时间。

If you need to perform calculations based on current time, simply use the Excel function NOW (). The result reflects the date and time when it is used.

以下 NOW () 函数使用情况的屏幕截图是在 2016 年 5 月 17 日下午 12:22 分拍摄的。

The following screen shot of Now () function usage has been taken on 17th May, 2016 at 12:22 PM.

obtaining current time

Obtaining Time from Hour, Minute and Second

您的数据可能分别包含有关时间、分和秒的信息。假设，您需要获取时间，将这 3 个值结合起来以执行任何计算。您可以使用方法 Time 获取时间值。

Your data might have the information about hours, minutes and seconds separately. Suppose, you need to get the Time combining these 3 values to perform any calculation. You can use Excel Function Time for getting the Time values.

obtaining time

Extracting Hour, Minute and Second from Time

您可以使用 Excel 函数 HOUR、MINUTE 和 SECOND 从给定时间中提取小时、分钟和秒。

You can extract hour, minute and second from a given time using the Excel functions HOUR, MINUTE and SECOND.

extract time

Number of hours between Start Time and End Time

当对时间值执行计算时，显示的结果取决于单元格中使用的格式。例如，您可以按如下方式计算上午 9:30 和下午 6:00 之间的小时数 −

When you perform computations on Time values, the result displayed depends on the format used in the cell. For example, you can compute the number of hours between 9:30 AM and 6 PM as follows −

number of hours

  1. C4 is formatted as Time

  2. C5 and C6 are formatted as Number.

时间差以天为单位。要转换为小时，您需要乘以 24。

You get the time difference as days. To convert to hours you need to multiply by 24.