How to Split Date and Time in Excel
🔵 If you download data into Excel, one column might have the Date and Time combined in each cell. In this short video, see 2 ways to quickly split that value into separate date and time columns.
✅ Get the sample Excel file to follow along
► https://myctx.link/SplitDate
💡 Related Links 💡
Split Date and Time in Excel ► https://myctx.link/SplitDate
More Excel Tips & Videos ► https://myctx.link/TipsXL
Excel resources I recommend ► https://myctx.link/xlpick
⏰ Video Timeline ⏰
0:00 Introduction
0:11 Split Date/Time With Formulas
1:30 Split Date/Time Without Formulas
2:52 Get the Sample File
🔶 Instructor: Debra Dalgleish, Contextures Inc.
💡 Get Debra's monthly Excel tips: http://www.contextures.com/signup01
Subscribe to Contextures YouTube: https://www.youtube.com/user/contextu...
#ContexturesExcelTips
'-------------------
VIDEO TRANSCRIPT
How to Split Date and Time in Excel
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.
Split Date With Formulas
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.
Get Time With Formula
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.
Split Date Without Formulas
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.
Get Time Without Formula
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.
Watch video How to Split Date and Time in Excel online without registration, duration hours minute second in high quality. This video was added by user Contextures Inc. 13 February 2016, don't forget to share it with your friends and acquaintances, it has been viewed on our site 225,42 once and liked it 1.1 thousand people.