Dates are a common problem in Excel, especially the format of the dates. This Excel tool allows you to easily change the format of dates.
- For updated video clips in structured Excel courses with practical example files, have a look at our MS Excel online training courses . You can even try the Free MS Excel tips and tricks course.
- To see if this video matches your skill level (see the suggested skill score below) do our free MS Excel skills assessment.
- If you are based in South Africa look at the live courses we offer in Johannesburg and Cape Town.
Date Text to Column
However my machine works with the assumption that dates are represented by day month year and as you can see this data has come through as month day year. Now instead of going through the whole process of deconstructing this and then reconstructing it to get the date right, you can trick Excel using the date Text to Column field.
Now normally this button is used to take a whole bunch of text and using Delimination or Fixed Width, force it into various columns but we can actually use it to change the date. So what I would do is highlight the cells that concern me and under Data you’ll see there is Text to Columns – click on it and in this case because we only have one column we actually trick Excel – neither of these (Delimited or Fixed Width) matter so click Next, the next window doesn’t matter, click Next. What is key here is that we can now tell Excel that these cells are actually dates, but more importantly in the drop down box , you can now say the dates are not day month year – they are actually month, day, year.
So I tell Excel to bring these through in one column (although they are actually already in one column). These are representative of the dates and this is actually month day year. When I click Finish, you’ll see what Excel does is brings it through and converts it into a date that my machine can actually work with.