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.


Download Practice Workbook

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


3 Methods to Separate Date and Time in Excel without Formula

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.

Use Text to Columns Wizard to Separate Date and Time

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

Use Text to Columns Wizard to Separate Date and Time

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

Use Text to Columns Wizard to Separate Date and Time

  • 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.

Use Text to Columns Wizard to Separate Date and Time

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

Use Text to Columns Wizard to Separate Date and Time

  • 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.

Use Text to Columns Wizard to Separate Date and Time

  • 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.

Read More: How to Separate Date from Text in Excel (4 Methods)


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.

Using Excel Flash Fill Feature to Separate Date and Time

  • Look at the dataset now.

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

Using Excel Flash Fill Feature to Separate Date and Time

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

Read More: How to Separate Time Using Formula in Excel (7 Ways)


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.

Separate Date and Time Using the Power Query Tool

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

Separate Date and Time Using the Power Query Tool

  • 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.

Separate Date and Time Using the Power Query Tool

  • 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.

Separate Date and Time Using the Power Query Tool

  • Finally, look at the dataset.

We separated dates and times successfully.


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 have a look at our website exceldemy.com and give your suggestions in the comment box.


Related Article

Alok

Alok

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo