This lesson is exclusive to members

Microsoft Excel 2016 Bootcamp - Zero to Hero Training

Date & currency formatting

Daniel Walter Scott || VIDEO: 1 of 24

Download Exercise Files Download Completed Files

You need to be a member to view comments.

Join today. Cancel any time.

Sign Up

Hello, wonderful people. In this video, we're going to clean up our dates. At the moment, we've got kind of a shortest version with 4 digits for the year, there's ones with 2 digits for the year. There's some long words, with the full word 'October' there, we need to clean all that up.

Another thing is, we'll add our dollar signs to this column here because it's our money. And it will look like this dollar signs, all consistent dates. So let's go and do that in this video.

Dates can be a bit of a problem, right? We've imported this data from who knows where. And some of the entries are in this format where it's got the written word 'May'. Some of them have got the days, months, year in 4 digits. Some of them have got them in just 2 digits. We need to get some sort of consistency. Doesn't really matter if the month is first, or the day is first. It's the same process.

So what we want to do is-- you might be lucky, and they might import, and all you have to do is select on it. And go up to here, and say, I want to go from long date to short date, and it might work. You might have lots of problems. Other problems where you've got other values, where I say, "Yes, let's just be the long date," and it just doesn't change. So we need to do something slightly more manual.

So you might have to do the easy way, and just go and change it, or this option here. So I'm going to select this one. Hold 'Shift' down on my keyboard, and select the last option. And then in here, I'm going to go to 'Data'. I'm going to go to the one that says 'Text to Columns'. It's reasonably easy. Leave it as 'Delimited', and hit 'Next'. Doesn't matter what this is, click 'Next'. And you're looking for this one here, say, I want dates to be day, month, year, 'DMY'. If you are in America, you might go month, day, year, ‘MDY', doesn't really matter. It's just going to force all these guys to be at least text that we can adjust. Hit 'Finish'.

And something kind of changes, you can see, nothing really happens, except now, when I go to 'Home'

and I go to 'Format', ‘Date', 'Short Date', they are all changing. And when I go to 'Long Date', they all become long dates. I'll scale this up, so you can see them all. So you might have to do it the quick easy way by just picking up here, 'Short' or 'Long' dates, or you might have to do my little trick here, go to 'Date'., and then, this 'Text to Column' option. Cool. Nobody likes long dates. And we go back to short dates.

And do our currency, so, back to here. Back to 'General'. 'Short Date'. Great. I'll make it a bit smaller again. Next thing we're going to do is this one. This one here is missing all the currency. So that's '4100'.

I'm just going to select this whole column by clicking 'D'. Up here, I'm going to pick 'Currency'. There we are. It's picked pounds '£' by default. You can go into here, and pick 'More Number Formats'. 'Currency', and I want not the £, I want English, doesn't really matter, all those symbols, we'll go to New Zealand.We love New Zealand. We set the dollar signs to it there. Lovely.

One thing you might also do is, can you see, it's added these '.00' to the end? That might be useful foryou if you've got change. In my case, just adds more data that I don't need. Makes it a little bit more confusing.

So I'm going to select the whole column again. And over here, under 'Home', just underneath, where wegot 'Currency' here, you can decide how many decimal points you want to show. You can show more, in our case we want to show less. Just down to the nearest dollar. Lovely, let's hit 'Save'.

And that my friends is how to format dates and currency formats, and other stuff. Let's look at cleaning up more of our table here in the next video.