If you have a file with dates
and times in the same cell,
we'll see two quick ways that you
can separate that into date and time
columns.
The first one will use a formula.
So in here I'm going to
click and type an equal sign.
We'll use the INT function,
which is for an integer and
that is going to pull the
whole number from this cell,
which is just the date
part of the date and time.
When I press Enter we'll see a
date. Now if you see a number,
you can format that as a date.
Then I can select that cell,
double-click on the Fill Handle,
and I very quickly have all the dates.
If a date has a time attached to it,
that's a decimal number.
So if it's 12 noon, it would
be the date number 0.5.
So we want to just get
that decimal portion,
so we'll type equals the date.
So that's the full member
including the decimal.
And then minus the date portion, we
want to subtract that full number.
So we'll use the INT
bracket and click on the
date. And when I press Enter,
we get the time. And again, you
might have to format that as time,
if you're just seeing a number.
And then fill that down.
We can also pull this
out without a formula.
So here's another copy of the same list.
And in here I'm going to just
type the first two dates.
So I'll type 2/1/16
and 2/3/16.
And now we're going to use the Flash Fill.
And if you're doing this with text, it
would fill down automatically, usually,
once we set a pattern,
but for numbers or dates,
it doesn't do that. We
can go to the Data tab,
and click Flash Fill,
and then it will suggest the
rest of the dates for you.
And then you can come here and accept
or undo or just leave it as is if you
want to accept it.
And we can do the same thing for the
time. So I'll type the first couple,
and 9:32.
And now again, I'll go
and click Flash Fill,
and it fills down the rest and they look
fine to me. So I'll leave those as is.
Now those won't change
if these dates change.
On the sheet where we used a formula,
if we update a time or the date,
because we have formulas
here, those would update.
For more Excel tips and tutorials and
to download the sample file for this
video, please visit my Contextures
website, at www.contextures.com.
