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 −
- 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
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 – 17th 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
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 17th 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.
