How to Separate Date and Time in Excel without Formula (3 Methods)

You can split date and time using both Excel formula or other Excel features. In this tutorial, we will show how you can separate date and time using Excel tools like Text to Columns, Flash Fill, and the Power Query.

How to Separate Date and Time in Excel without Formula: 3 Suitable Methods

Assume that we have a dataset that contains the date and time in the same cell.

Now, we will discuss 3 easy methods to separate date and time using different Excel features.

1. Use Text to Columns Wizard to Separate Date and Time

Text to Columns is an interesting feature of MS Excel. Usually, this feature is used to separate objects from a single cell. We will apply this feature here.

Steps:

• First, add a new column named Time.
• Select all the cells of the Date & Time column.
• Choose the Data Tools group from the Data.
• Select the Text to Columns option.

• We enter the Text to Columns Wizard window.
• Choose the Fixed width option and then press Next.

• 2nd step of the Text to Columns appears now.
• Go to the Data preview.
• Choose the desired section. Then press the Next button.

• Choose the General option of step 3.
• Press the Finish button.

• We can see that Time and Date are separated into two columns.

But there is a problem that 00:00 time is present with the date value and 01/00/1900 is attached with the time value. 00:00 is the initial time and 01/00/1900 is the initial date considered by MS Excel. Now, we will change the format of both columns to get our desired value.

• Choose all the cells of the Date & Time column.
• Press the right button of the mouse.
• Choose the Format Cells option from the Context Menu.

• Now, choose our desired Date format from this Format Cells window.
• Then, press OK.

• Look at the dataset.

Dates are formatted as required.

• Now, select the cells of the Time column and again go to the Format Cells window.
• Then, choose the required Time format from the window.

• Have a final look at the dataset.

The time and date both are separated here. We can also call the Format Cells option by pressing Ctrl+1.

2. Using Excel Flash Fill Feature

The Flash Fill is another amazing feature of Microsoft Excel. This feature senses a given pattern and fills our selected range.

Follow the steps below.

Steps:

• Add two columns named Date and Time.
• Fill up the first two cells of the Date and Time column from the Date & Time column.

• Now, select all the cells of the Date column.
• Go to the Data tab.
• Choose the Flash Fill option from the Data Tools group.

• Look at the dataset now.

• Similarly, choose all cells of the Time column and apply Flash Fill as shown before.

We separated the date and time. We could press Ctrl+ E too to Flash Fill.

3. Separate Date and Time Using the Power Query Tool

Power Query is a widely used tool of MS Excel. Power Query can easily separate data and time in Excel too.

Steps:

• Select all the cells of the Data & Time column.
• After that, choose From Table/Range from the Data tab.

• A pop-up appears showing the range to create a table.

• The Power Query window appears.

• Select the Date & Time column and go to the Add Column tab.
• Look, at the Date option in the upper right corner. Choose the Date Only option.

• The Date column was added here. It contains dates only.

• Again, choose the Date & Time column and select Add Column tab.
• Look at the upper right corner. We get Time Only option from the Time group.

• Finally, look at the dataset.

We separated dates and times successfully.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Conclusion

In this article, we described 3 methods to separate date and time without any formula in Excel. I hope this will satisfy your needs. Please give your suggestions in the comment box.

<< Go Back to Date and Time | Split | Learn Excel