Importing Data into Excel: Fixing the Date

Quick reference

• 00:04 Data errors in the date column are a common issue.
• 00:07 If you open a sheet from a computer with different regional settings than yours,
• 00:12 you've probably noticed this error before.
• 00:14 It takes a little bit of manipulation to get it fixed,
• 00:17 especially because there are so many different date formats.
• 00:21 The first thing you need to know is that Excel regards a date as a number,
• 00:27 the 1st day for Excel and the 20th day for Excel.
• 00:32 Well, let's see what that means.
• 00:35 The 1st day for Excel was the 1st of January 1900,
• 00:40 and the 20th day is the 20th of January 1900.
• 00:44 So why did I show you the 20th?
• 00:47 Well, because when you look at the first date, it's not clear what the regional
• 00:51 settings for this computer are, if they're month, day, year or day, month, year.
• 00:56 But when you see the 20th, it's clear, the settings for
• 00:59 this computer are month, day, year.
• 01:02 So if you share Excel files between computers with different regional
• 01:06 settings, here are a couple of problems you might encounter.
• 01:11 If the days are smaller than 12, Excel might recognize the days as the months,
• 01:17 and then your dates are incorrect.
• 01:21 But when the days are larger than 12,
• 01:23 Excel might not understand what these are, and just import them as text.
• 01:30 So here's my recommendation.
• 01:32 When you send a file or when you share a file and you're not sure of the other
• 01:36 person's regional settings, change the format of your date to a number.
• 01:41 Then it will send the correct number,
• 01:43 which is the total number of days since January 1900.
• 01:46 And the recipient will get it, and
• 01:48 you can tell them to change that column to a date format.
• 01:52 And then they change the column to a date, and if their regional settings are month,
• 01:56 day, year and
• 01:57 yours are day, month, year, well, then the differences in those formats won't matter.
• 02:03 The dates will import correctly.
• 02:05 If that's not possible, you end up with a situation where you have bad dates,
• 02:09 there's a way to fix it.
• 02:11 We're going to fix this in a two-step process.
• 02:14 First, we're going to break this up in its different parts.
• 02:17 And then we're going to put them back together so that Excel knows it's a date.
• 02:22 First, splitting, we'll select the dates, we'll click on the Data tab.
• 02:27 And then there's a Data Tools grouping, and an icon, Text to Columns.
• 02:32 We click that, what it's going to do is separate the data.
• 02:37 So how do I convert this?
• 02:38 Is it delimited or fixed width?
• 02:40 Well, it's definitely not fixed width.
• 02:42 Look at the bottom dates.
• 02:43 They are fixed, two digits for the day, two digits for the month, and
• 02:48 four to indicate the year.
• 02:49 But the top dates are not fixed.
• 02:52 Some days have two digits, and some only have one.
• 02:56 But we do have a delimiter, and therefore a way to split these up.
• 03:00 Every time we see a slash, that is a potential split, that's our delimiter.
• 03:06 So what is the delimiter here, we'll click on Other, type in /.
• 03:11 And look what happens to the dates below.
• 03:13 Now each part of the date is in its own column.
• 03:17 Next, don't worry about the format because we're going to fix the format in
• 03:20 the next step.
• 03:21 And now we choose where to put these split cells.
• 03:25 If we put it just in A5, it's going to replace the text in B and C.
• 03:30 We don't want to do that.
• 03:31 We'll place these new values in E5.
• 03:34 I have three blank columns there.
• 03:36 So when we click Finish, the values go there.
• 03:41 Now the month and days and years are all split.
• 03:45 But now, the next step, how do we put it all together?
• 03:48 Well, there's a nice date function you can use.
• 03:50 We type in date, open parentheses, then we select the year.
• 03:54 2020 is the year, we click on that.
• 03:57 Which is the month, that'll be the second column, we click that.
• 04:02 And then the day is the first column, we click that, close parentheses, hit Enter,
• 04:07 and it looks great.
• 04:09 We can copy that down to fill out the rest of this top section,
• 04:12 but not the bottom section because remember, it was a different format.
• 04:16 Now we have to select the columns in a slightly different order.
• 04:20 The year's the same, but the month will be in the first column, and
• 04:25 the day in the second column.
• 04:27 When we complete that function, now that looks great, and
• 04:31 we copy it down, and the dates look fantastic.
• 04:36 Now we're going to put our dates in the first column where they should go.
• 04:40 So we're going to select our dates, hit Ctrl+C to copy.
• 04:44 We'll click into the first row in that first column,
• 04:47 make sure you put these values in the right place.
• 04:51 But we're not going to just hit Ctrl+V to paste, because that would paste
• 04:56 those cells that have formulas in them into those cells in the first column,
• 05:00 and that wouldn't work.
• 05:02 What we need to do is paste the values only.
• 05:05 So we're going to go down to paste special, and we'll do values only.
• 05:09 We'll hit OK, and now you see those dates all get pasted over,
• 05:14 they're all in the proper format.
• 05:17 We can delete these other columns over here on the right because we don't need
• 05:20 them anymore.
• 05:22 And now our dates look perfect.
• 05:25 Now in the next video,
• 05:27 we're going to look at how to get rid of the blank lines in our data.

Lesson notes are only available for subscribers.