4-1: Change Date Format in Excel (Apr-10-21, 04/10/21, 10/4/21 etc) - YouTube

You may have noticed that I've been very careful to use internationally safe Date Formats throughout this course.

A classic cause of errors, when dealing with international worksheets, is the use of a date such as the one shown in cell D4: 3/10/2008.

This means the 10th of March 2008 in some countries such as the USA,

and the 3rd of October 2008 in others, such as the United Kingdom.

If your work may be viewed by an international audience, it's far better to use a date format that cannot possibly cause confusion.

In this lesson we'll reformat a date into the compact and universally readable format of 10- Mar-08 shown in cell E4.

So let's open the sample file for this lesson.

The sample file is: Sales Week Ended the 14th of March 2008, and you'll find it in the Session 4 Sample Files folder.

Notice that this worksheet has a few problems.

First of all, the dates shown in Column B are too wide for the column.

In the case of dates they are shown as a row of hashes when this occurs.

You can also see that Column C is far too narrow for its contents.

So the text in Column C has been truncated.

And in Column E we have the reverse problem, where the column's too wide.

So the column header "Amount" appears too far to the left of the column.

Let's repair all of these problems in one operation using the skill you learned in Lesson 2-9.

I'll select cells A1 to G17 and then it's the Home tab on the Ribbon and, in the Cells group, Format, and then: Autofit Column Width.

And everything now looks fine.

Look at the dates in Column B.

They're very verbose.

Let's look at the date in cell B2.

Instead of "Monday, March 10, 2008", I'd like to show "Mar-10-08".

So I'll select all of the cells in Column B and I'll bring up the Format Cells dialog.

Now the fastest and most convenient way to do this is to right click inside the selected cells

and then select Format Cells… from the Shortcut Menu.

And when you do this, the Format Cells dialog is displayed.

You could also do this from the Ribbon by selecting the cells once again

and then on the Home tab on the ribbon, in the Number group, you can click the Dialog Launcher.

And exactly the same dialog is then displayed.

But I’ve found the right click method to be far more convenient.

And it's the one that I use all the time when I'm formatting cells.

When the Format Cells dialog is displayed, you should see that the Number tab is selected.

If it's not, just click on the tab to select: Number.

Notice that the Category has also been preselected to Date.

Once again, if you don't see Date here just click on Date to select it.

And on the right hand side of the dialog you can see several different date formats.

And I can see the format that I want towards the top of the list.

It's this one: 14-Mar-12.

Now the date you're seeing on your system may be different to the ones I'm seeing here.

That's because the date format you see will depend upon your locale.

This computer has a locale of English United States, so I'm seeing the format: 14-Mar-12.

But it's quite possible that your computer will have a different locale, such as English United Kingdom,

and in that case you'll have to set the date format manually.

And here's how you'd do it.

You can click: Custom, from the bottom of the Category box and then scroll up and down the Customs Formats available.

The Excel formatting strings are very complex but you can probably see that several of these relate to numbers and some relate to dates.

The ones relating to dates have letters such as m, d and y for Month, Day and Year.

I'm going to click on one of them: m/d/yyyy.

And when I do, you can see a sample appears in the top box showing me the sort of date that would result if I chose this format.

I'm going to press the Down Arrow key to move down the list and you can see that that second format seems to work well.

It's "d-mmm-yy".

That means: "Show the date, followed by a dash, followed by the month spelt as three letters, followed by a dash,

followed by the year, spelt as two numbers", so I think that format would work for me.

So in a UK locale I'd now click OK and all my dates would be formatted correctly.

Now I'll resize Column B so it's exactly the right width for the new format.

To do this I'll hover over the intersection of columns B and C and then double click with the mouse to automatically size the column.

All that remains now is to save your work and I'll save this file with the new name: Sales Week Ended the 14th of March 2008-1.

And as usual I'll save to the folder above my Sample Files Folder.

I click the Save button and you've now completed Lesson 4-1: Format Dates.