In this article, we will delve into various techniques for splitting date and time in Microsoft Excel -

Download the Excel Workbook below to follow along and understand How to Separate Date and Time in Excel -

download excel workbookSeparateDateandTime.xlsx

Text to Columns

Highlight the cell or column that contains the combined date and time information.

Navigate to the Data tab and select Text to Columns.

Opt for Delimited in the wizard and then press Next.

Choose the appropriate delimiter that divides date and time (e.g., space or a comma). Here, we'll choose Space, then click Next.

At this point, decide where you want the separated data to be placed. By default, it will overwrite the existing data. However, you can specify C2 as the starting cell.

Click Finish.

Click here to learn how to Split First & Last Name Using Text to Columns!

LEFT and RIGHT Functions

The LEFT and RIGHT functions can be employed to pull out specific parts of text data. Assuming the date is on the left side of the combined data, you can use the LEFT function to extract it into column B.

In cell B2, input =LEFT(A2,5)
(The number 5 indicates the number of characters taken from the left, leaving only the date value)

You'll notice the data appears in a different format. Before converting it to a date format, drag the formula down to the end of the column.

Select the cells in column B, right-click, and choose Copy.

Right-click once more, and select Paste Special then Values.

Click on the warning icon, and choose Convert to Number.

Right-click and select Format Cells. Choose Date. Click OK.

To extract the time, employ the RIGHT function. Assuming the time is on the right side of the combined data, you can use the RIGHT function to extract it into column C.

In cell C2, enter =RIGHT(A2,12)
(The number 12 signifies the number of characters taken from the right, leaving only the time value)

You'll see the data appears in a different format. Before converting it to a time format, drag the formula down to the end of the column.

Select the cells in column C, right-click, and choose Copy.

Right-click again, and select Paste Special then Values.

Click on the warning icon, and choose Convert to Number.

Right-click and select Format Cells. Choose Time. Click OK.

Click here to Unlock the Power of Excel: 8 Substring Functions for Easy Data Analysis

INT and MOD Functions

Suppose we have the date and time values in Column A. To extract the date portion, utilize the INT function.

In cell B2, enter =INT(A2)

Double-click or click and drag the small square at the bottom-right corner of cell B2 to apply the formula to the rest of the column.

(You might observe that the format still shows the time as 00:00:00. To correct this, right-click and select Format Cell. Choose Date.)

To extract the time component, use the MOD function. In cell C2, input =MOD(A2,1)

Double-click or click and drag the small square at the bottom-right corner of cell B2 to apply the formula to the rest of the column.

(The format might still show the date. Right-click and select Format Cell. Choose Time.)

Click here to learn How to get Remainder in Excel using MOD Formula!

By default, these formulas will be linked to the original values. If you change the values in Column A, it will directly reflect the changes in Column B and C. The formula is dynamic.

If you want Columns B and C to be independent of Column A, select B2 to C7. Right-click and Copy. Right-click again, and select Paste Special then Values.

Power Query

Power Query is an effective data transformation tool in Excel. It can be used to split and manipulate date and time data effortlessly. You can utilize the Split Column feature to divide the data based on specific criteria.

Select Column A

Go to the Data tab and click on Get Data or Get & Transform Data.

Click on From Sheet.

Navigate to the Add Column tab. Click on Date, then Date Only.

Go back to the Original column and select Time. Click on Time Only.

Click here to learn how Split a Query into Two Parts in Power Query!

Return to the Home tab.

Click the Close & Load button to apply the changes and load the data back into your Excel worksheet.

Conclusion:
There you have it! Separating date and time in Excel is a straightforward yet valuable skill when dealing with combined date-time data. Whether you opt for basic methods like Text to Columns and text functions or advanced techniques involving Power Query, Excel provides a variety of tools to help you efficiently manage your date and time data.

Click here to learn how to use the INT and FIX functions in Excel!

The above is the detailed content of How to Separate Date and Time in Excel. For more information, please follow other related articles on the PHP Chinese website!