- Excel Data Analysis Tutorial
- Excel Data Analysis - Home
- Data Analysis - Overview
- Data Analysis - Process
- Excel Data Analysis - Overview
- Working with Range Names
- Tables
- Cleaning Data with Text Functions
- Cleaning Data Contains Date Values
- Working with Time Values
- Conditional Formatting
- Sorting
- Filtering
- Subtotals with Ranges
- Quick Analysis
- Lookup Functions
- PivotTables
- Data Visualization
- Data Validation
- Financial Analysis
- Working with Multiple Sheets
- Formula Auditing
- Inquire

- Advanced Data Analysis
- Advanced Data Analysis - Overview
- Data Consolidation
- What-If Analysis
- What-If Analysis with Data Tables
- What-If Analysis Scenario Manager
- What-If Analysis with Goal Seek
- Optimization with Excel Solver
- Importing Data into Excel
- Data Model
- Exploring Data with PivotTables
- Exploring Data with Powerpivot
- Exploring Data with Power View
- Exploring Data Power View Charts
- Exploring Data Power View Maps
- Exploring Data PowerView Multiples
- Exploring Data Power View Tiles
- Exploring Data with Hierarchies
- Aesthetic Power View Reports
- Key Performance Indicators

- Excel Data Analysis Resources
- Excel Data Analysis - Quick Guide
- Excel Data Analysis - Resources
- Excel Data Analysis - Discussion

- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who

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

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

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

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

Click the

**Number**tab in the**Format Cells**dialog boxClick

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

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

Using

**Format Cells**dialog boxUsing Excel

**TIMEVALUE**function

Click the

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

**General**under**Category**.

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

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.

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.

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

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.

Advertisements