Pinterest Pixel

How to Separate Date and Time in Excel

There are situations when you need to split a date and time that are combined into a... read more

Download Excel Workbook
John Michaloudis
Posted on

Overview

How to Separate Date and Time in Excel | MyExcelOnline

There are situations when you need to split a date and time that are combined into a single cell or column. This could be done for various reasons, including:

– Performing calculations or analysis on the date and time components separately.
– Formatting the date and time differently for presentation or reporting.
– Integrating date and time data into other applications or systems that require them to be separate.

In this article, we will explore various methods to separate date and time in 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

Select the cell or column containing the combined date and time data.

How to Separate Date and Time in Excel

Go to the Data tab and click on Text to Columns.

How to Separate Date and Time in Excel

Choose Delimited in the wizard then select Next.

How to Separate Date and Time in Excel

Select the appropriate delimiter that separates date and time (e.g., space or a comma). In our case we will select Space, then click Next.

How to Separate Date and Time in Excel

From here, we need to decide where we want the split data times to appear. By default it will overwrite the existing values. But we can change it to C2 instead.

How to Separate Date and Time in Excel

Click Finish.

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

See also  Best Tips & Tricks on Using Nested IF Statement in Excel

How to Separate Date and Time in Excel

LEFT and RIGHT Functions

You can use the LEFT and RIGHT functions to extract specific portions of text data. Assuming the date is on the left side of the combined data, you can use the LEFT function to extract it to column B.

In cell B2, enter =LEFT(A2,5)
(The number 5 represents the number of characters removed from the right. Essentially, only leaving the date value)

How to Separate Date and Time in Excel

As you can see the data comes out in a different code. Before we can change this into a date format, we will drag the formula all the way down to the bottom of the column.

How to Separate Date and Time in Excel

Highlight the cells in column B, right-click, select Copy.

How to Separate Date and Time in Excel

Right-click again, and select Paste by Value.

How to Separate Date and Time in Excel

Click on the caution box, and select Convert to Numbers.

How to Separate Date and Time in Excel

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

How to Separate Date and Time in Excel

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

In cell C2, enter =RIGHT(A2,12)
(The number 12 represents the number of characters removed from the right. Essentially, only leaving the time value)

How to Separate Date and Time in Excel

As you can see the data comes out in a different code. Before we can change this into a date format, we will drag the formula all the way down to the bottom of the column.

See also  Unlock the Power of Excel: 8 Substring Functions for Easy Data Analysis

How to Separate Date and Time in Excel

Highlight the cells in column C, right-click, select Copy.

How to Separate Date and Time in Excel

Right-click again, and select Paste by Value.

How to Separate Date and Time in Excel

Click on the caution box, and select Convert to Numbers.

How to Separate Date and Time in Excel

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

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

How to Separate Date and Time in Excel

How to Separate Date and Time in Excel

INT and MOD Functions

Let’s say we have the date and time values in Column A. To get the date portion, we can use the INT function.

In cell B2, enter =INT(A2)

How to Separate Date and Time in Excel

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

How to Separate Date and Time in Excel

(You may notice that the format is still showing the time as 00:00:00. To remove this, right-click and select Format Cell. Select Date.)

To get the time element, we can use the MOD function. click on cell C2, and enter =MOD(A2,1)

How to Separate Date and Time in Excel

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

How to Separate Date and Time in Excel

(The format will may show the date. Right-click and click on Format Cell. Choose Time.)

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

See also  Delete Steps Until End In Power Query

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.

How to Separate Date and Time in Excel

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

How to Separate Date and Time in Excel

How to Separate Date and Time in Excel

Power Query

Power Query is a powerful data transformation tool available in Excel. It can be used to split and manipulate date and time data with ease. You can use 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.

How to Separate Date and Time in Excel

Click on From Sheet.

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

How to Separate Date and Time in Excel

How to Separate Date and Time in Excel

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

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

How to Separate Date and Time in Excel

How to Separate Date and Time in Excel

Go back to the Home tab.

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

How to Separate Date and Time in Excel

Conclusion:
There you have it! Separating date and time in Excel is a simple and useful skill when dealing with combined date-time data. Whether you use basic methods like Text to Columns and text functions or advanced techniques involving Power Query, Excel offers a wide range of tools to help you effectively manage your date and time data.

See also  Remove Columns Using Power Query or Get & Transform

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

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

How to Separate Date and Time in Excel | MyExcelOnline
Founder & Chief Inspirational Officer at MyExcelOnline.com

John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship Academy Online Course.

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!