How to separate date and time values in Excel 365 [with Examples]?

In today’s Excel Data Analysis tutorial, we’ll learn how to split cell containing date values into separated cells. In this tutorial, we will focus on accomplishing our tasks with simple Excel formulas, although in more advance scenarios we could use Power Query for Excel or a VBA macros as well. We also added an option to use the Flash Fill capapbility that you can use to split datetime cells as well.

Option 1: Split dates using the TEXT function

The TEXT function is extremely useful for formatting dates. In our case we can extract the date and time in a following way:

Dates:

=TEXT(A3,"mm/dd/yyyy")

Times:

=TEXT(A3,"hh:mm:ss")

Option 2 : Separate date and time using the INT function

When applied to a date time cell, the in function allows to extract the date value.

Dates:

=INT(A3)

Times:

If you are using INT to get the data values, then you can just substract the date value from the full date to obtain the time:

=A3 - INT (A3)

Option 3: Using TRUNC to get dates and time cells

TRUNC follows a similar logic than the INT function, which we just reviewed.

Dates:

=TRUNC(A3)

Times:

=A3 - TRUNC (A3)

Option 4: Using Flash fill

A nice way to obtain similar results, without the need to type formulas is to use the Flash Fill capability available in the Data tab.

Start by manually filling in a few dates and times in your spreadsheet.

Then, click the next empty cell in your column, go on to the Data tab and invoke the Flash Fill capability to fill in the remaining cells in the column.

Flash Fill is not always intuitive and its suggestions might not fit your needs so always check carefully its output before moving on to other tasks.

Suggested Learning