5 Ways To Extract Time From Date Time In Excel

Extracting time from a date-time value in Excel can be a common requirement in various data analysis tasks. Whether you're dealing with timestamps, schedules, or logs, isolating the time component can help you analyze and present data more effectively. Excel provides several methods to achieve this, catering to different user needs and data formats. Here are five ways to extract time from a date-time value in Excel.

Method 1: Using the TIME Function

Using the TIME Function

One of the most straightforward methods to extract time from a date-time value is by using the TIME function. The TIME function takes three arguments: hour, minute, and second. When used in conjunction with the HOUR, MINUTE, and SECOND functions, you can effectively extract the time component from a date-time value.

Example:

Suppose you have a date-time value in cell A1: 2023-02-20 14:30:00. To extract the time component, you can use the following formula:

=TIME(HOUR(A1), MINUTE(A1), SECOND(A1))

This formula will return the time component 14:30:00.

Method 2: Using Text to Columns

Using Text to Columns

Another method to extract time from a date-time value is by using the Text to Columns feature. This method is particularly useful when dealing with text-based date-time values.

Example:

Suppose you have a text-based date-time value in cell A1: 2023-02-20 14:30:00. To extract the time component, follow these steps:

1. Select cell A1.
2. Go to the Data tab in the ribbon.
3. Click on Text to Columns.
4. Choose Delimited Text.
5. Select Space as the delimiter.
6. Click Finish.

The time component 14:30:00 will be extracted and placed in a new column.

Method 3: Using the MOD Function

Using the MOD Function

The MOD function can be used to extract the time component from a date-time value by taking advantage of the fact that Excel stores dates and times as serial numbers. By using the MOD function, you can extract the remainder of the division of the date-time value by 1, which effectively gives you the time component.

Example:

Suppose you have a date-time value in cell A1: 2023-02-20 14:30:00. To extract the time component, you can use the following formula:

=MOD(A1, 1)

This formula will return the time component 0.6, which can be formatted as 14:30:00.

Method 4: Using the INT and MOD Functions

Using the INT and MOD Functions

This method involves using the INT and MOD functions in conjunction to extract the time component from a date-time value.

Example:

Suppose you have a date-time value in cell A1: 2023-02-20 14:30:00. To extract the time component, you can use the following formula:

=MOD(A1, 1) + INT(A1) * 0

This formula will return the time component 14:30:00.

Method 5: Using Power Query

Using Power Query

Power Query is a powerful data manipulation tool in Excel that allows you to extract and transform data in various ways. One of the methods to extract time from a date-time value is by using the Power Query Editor.

Example:

Suppose you have a date-time value in column A. To extract the time component, follow these steps:

1. Go to the Data tab in the ribbon.
2. Click on From Table/Range.
3. Select the date-time column.
4. Click on OK.
5. In the Power Query Editor, click on the Transform tab.
6. Click on Time.
7. Click on Extract Time.

The time component will be extracted and placed in a new column.

Gallery of Time Extraction Methods:

Time Extraction Methods Gallery

In conclusion, extracting time from date-time values in Excel can be accomplished using various methods, each with its own strengths and weaknesses. By choosing the most suitable method for your specific needs, you can efficiently and accurately extract the time component from date-time values in your data.