This lesson is exclusive to members

Microsoft Excel 2016 Bootcamp - Zero to Hero Training

Date 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

In this video, we're going to look at formatting the date where the days are in the front, and months, second, and vice versa. So let's go and do that in Excel now.

That's not hard, but there's some formatting issues that pop up sometimes. I'm going to put in the date here, I'm just going to manually type in '4/4', and I'm going to put in '17'. Press 'return', and it does some things where-- you can see, it's auto-formatted it, it's added the year, '2017' when I just put in '17'. You might like that, you might not, but it's easy to go and change.

You'll see up here, whereas before, all these cells were set to 'General', this one's automatically been changed to 'Date'. We can drop this down, go down the bottom where it says 'More Number Options', it’s defaulted to 'Date', you can see, these are the different formats. You might want to go back to where you had it before where you were just using the two digits for the last date here.

And, what else, there's a couple of other ways, where it removes '0', it's up to you, the formatting that you want. Down the bottom here, this is the location, so at the moment, mine's giving the date first, then the month, then the year. If you're based in US, you do the month first, right? Crazy, but yes, we do. You can change that down here, where it says 'English (United Kingdom)', and you can switch it out to 'English (United States)'. And that will switch that around. I'm going to leave mine on 'United Kingdom'.

One thing we will do is-- so first, if I switch it back to this one here, kind of the format that I first chose, doesn’t matter what I put in there now. So I can go in here, and double click it all. And I can say, actually, I want '4/4/2017'. The long version, and it will always format it, be consistent.

Another thing you can do is, with it selected, I'm going to go to 'Date', there's a long version, 'Long Date'. Click on this one. Now, you'll notice mine has gone to hashes '####'. All that means is that I cannot fit in there. So it's showing data that can't fit. Says, not enough room, so I need to make it wider. If I make it too much wider it's going to go off, and be on this page. Not good.

So what I'm going to do is, with this cell here, I'm going to grab it at the top here. Next to 'H', drag along to give myself a little bit more room. So I can drag it out, and hopefully all my dates fit in there. You need to leave enough room for the longest date. I don't know what that one is. September, that's a guess. So we’re going to need to leave a lot more room probably for that. So I'm going to go along, move a few of these along just so I've got enough room for the word 'September'. So that's formatting the dates.

What you could do is, you could put in today's date automatically. So instead of having this date here-- I'm going to delete that. And up the top where it says 'Formulas', there's one here called 'Date & Time', and I can say 'Today'. That will just calculate. Click 'OK'. That will calculate whatever date it is today.

The problem is, when I open this quote it's always going to be today's date. Say you might require to open it in two years, and this date's going to change. It's up to you, if you like this automatically filling in. Bear in mind, the date's changing whenever you reissue a quote. That's fine. I'm going to 'undo' mine and go back to how I had it before.

There is another way of getting around this. Say you hate the formatting, the formatting's driving you bananas, you just want it to do what you want it to do. It's under 'Home', where it says 'Date', go back to this one that says 'Text'. 'Text' is just as you type it. If I put '14/04/2017', it’s not going to change it, it's going to be exactly how you want it. And that might actually just be the easiest way to do it for this quote.

So that's working with dates in Excel 2016. Let's go on to the next video.