使用时间值

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 −

Time Formats
- Time in Serial Format
- Time in Hour-Minute-Second Format
Converting Times in Serial Format to Hour-Minute-Second Format
Converting Times in Hour-Minute-Second Format to Serial Format
Obtaining the Current Time
Obtaining Time from Hour, Minute and Second
Extracting Hour, Minute and Second from Time
Number of hours between Start Time and End Time

Time Formats

Excel supports Time Values in two ways −

Serial Format
In various Hour-Minute-Second Formats

You can convert −

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

Time in Serial Format

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.

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

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

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

4:00:00 PM
16:00
4:00 PM
16:00:00
5/17/16 4:00 PM
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 −

Click the Number tab in the Format Cells dialog box
Click Time under Category.
Select the Locale. Available Time formats will be displayed as a list under Type.
Click on a Format under Type to look at the Preview in the box adjacent to Sample.

After choosing the Format, click OK

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

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

Using Format Cells dialog box
Using Excel TIMEVALUE function

Using Format Cells dialog box

Click the Number tab in the Format Cells dialog box.
Click General under Category.

Using Excel TIMEVALUE Function

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") results in 0.70503472

Obtaining the Current Time

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.

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

Obtaining Time from Hour, Minute and Second

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.

Extracting Hour, Minute and Second from Time

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

Number of hours between Start Time and End Time

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 −

C4 is formatted as Time
C5 and C6 are formatted as Number.

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