Скачать с ютуб How to Split Date and Time in Excel в хорошем качестве

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.