Locked lesson.
About this lesson
Use the text to columns button to separate the different parts of the date and the DATE function to put it back together as a proper date.
Quick reference
Importing data into Excel: fixing the date
Learn to recognize incorrect dates and fix them when importing files.
When to use
This is useful to know if you received a file from an external source and the date fields are seen as text and you don't want to retype the data.
Instructions
Fixing dates
- Excel regards dates as serial numbers, and auto aligns imported, recognized dates to the right-hand side of a cell just like numbers
- Different regional settings on computers and a range of other issues can cause dates to import as text
- The fix for this is usually linked to the problem
In the video
- Separate the elements of the date using text to columns
- Add the elements of the date together again using a DATE function
- Separate DATE functions for the two different date problems
- 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.