Extract Time from Date and Time in Excel: A Quick Reference

SenseBridge

3 min read

·

26-10-2024

1

0

Share

Extracting time from a date and time in Excel can be a common requirement for many users working with time-based data. Whether you’re a business analyst, accountant, or a student managing schedules, knowing how to isolate time components can greatly enhance your data handling skills. In this guide, we’ll explore various methods to extract time in Excel, including formulas and built-in functions.

Understanding Excel Date and Time Format 🕒

Excel stores dates and times as serial numbers. The integer part represents the date, while the decimal part represents the time. For example, the date and time "January 1, 2023, 12:30 PM" is stored as a serial number with an integer for the date and a decimal for the time.

How to Identify Date and Time Formats

In Excel, you can identify whether a cell contains date and time by checking its format. Here’s how you can do this:

1. Select the cell.
2. Right-click and choose Format Cells.
3. Under the Number tab, check if it’s set to a date or time format.

Understanding the format is crucial for the extraction process.

Extracting Time Using Formulas ⏱️

Using the TEXT Function

One of the simplest ways to extract time is by using the TEXT function. The TEXT function converts a number to text in a specific format.

Formula:

=TEXT(A1, "hh:mm:ss")

Where A1 is the cell that contains the date and time.

Example:

If cell A1 contains 01/01/2023 12:30:45, the formula would return 12:30:45.

Using the HOUR, MINUTE, and SECOND Functions

If you want to extract specific time components, you can use the HOUR, MINUTE, and SECOND functions.

Example Formulas:

• HOUR:

=HOUR(A1)

• MINUTE:

=MINUTE(A1)

• SECOND:

=SECOND(A1)

Using these functions will return integer values. For the date 01/01/2023 12:30:45:

• =HOUR(A1) will return 12
• =MINUTE(A1) will return 30
• =SECOND(A1) will return 45

Combining to Get Time in One Cell

If you need to combine hours, minutes, and seconds back into a single time format, you can use the following formula:

Formula:

=HOUR(A1) & ":" & MINUTE(A1) & ":" & SECOND(A1)

This will return 12:30:45 as a text string.

Using the INT Function to Extract Time 🕑

Another effective method is utilizing the INT function along with subtraction. This method helps to isolate the time component as well.

Formula:

=A1 - INT(A1)

This formula subtracts the date from the full date and time, resulting in only the time. You will need to format the resulting cell to a time format (hh:mm:ss) to see the output correctly.

Example:

Given that cell A1 has 01/01/2023 12:30:45, applying the formula above would yield 12:30:45 if formatted as time.

Using Excel’s Built-in Tools 🌟

1. Text to Columns

If you have a list of dates and times in a single column and want to separate them, you can use the Text to Columns feature.

1. Select the column that contains the date and time.
2. Go to the Data tab.
3. Click on Text to Columns.
4. Select Delimited and click Next.
5. Choose a delimiter (like space or comma, depending on your data).
6. Click Finish.

This action will split the data into multiple columns where you can further isolate times.

2. Format Cells Option

Sometimes, simply changing the format of the cells can display only the time:

1. Select the cell or range of cells.
2. Right-click and select Format Cells.
3. Under the Number tab, choose Time and select your preferred format.
4. Click OK.

This will visually display only the time, although the underlying value remains unchanged.

Practical Examples of Time Extraction 🔍

Important Note:

Always ensure the original date-time format is recognized by Excel for the formulas to work correctly.

Summary

Extracting time from a date and time in Excel can be accomplished through various methods. Whether using simple formulas like TEXT, HOUR, MINUTE, and SECOND, or employing tools like Text to Columns, users have multiple ways to achieve their goals.

Understanding how Excel handles date and time internally will allow for more effective manipulation of data. As you practice these techniques, you’ll find that you can quickly extract time components to analyze your data more efficiently.