How to change date formats in Microsoft Excel

Did you receive a document with the dates in the wrong format? Maybe the mistake is yours, or you just want to go another way. Whatever the reason, you can quickly and easily change the date format in Microsoft Excel. You can change it to a specific data series in a spreadsheet or change the standard date format on your computer to apply to all of your new spreadsheets.

Steps

Method 1 of 2: Change the standard date format

Step 1. Go to the date and time settings

To change the standard date format on any new Excel worksheet, you need to change the primary date format on your computer. First click on To start up. The next step will depend on the operating system you are using.

• If you are using Windows Vista or Windows 8: open the control panel and click on Clock, language and region. In Windows 8, you can also open the Settings folder and select Time and language.
• If you are using Windows XP: open the control panel and select Regional options, date, time and language.

Step 2. Go to regional options

Again, the step to take depends on the operating system.

• If you are using Windows 8: in the “Clock, language and region” folder, select Change the format of the date, time and numbers under the heading "Region".
• If you are using Windows Vista: open the "Regional and language options" dialog box and click on the tab Formats.
• If you are using Windows XP: open the "Regional and language options" dialog box and select the tab Regional options.

Step 3. Prepare to customize the format

If you are using Windows 8, make sure that the tab Formats is open. If you are using Windows Vista, click Customize this format. If you are using Windows XP, click Personalize.

Step 4. Choose a date format

You have the choice between a short date and a long date. The short date is the abbreviated format (eg 12/6/2015). The long date is the more written version (eg December 31, 1999). The format you select here will apply to all Windows applications, including Excel. Click on OK to apply your changes.

• Review the short date options. June 2, 2015 will be used as an example with all possible combinations (in English and French).
  • M / d / yyyy: 6/2/2015
  • M / d / yy: 6/2/15
  • MM / dd / yy: 06/02/15
  • MM / dd / yyyy: 2015-02-06
  • yy / MM / dd: 06/15/02
  • yyyy-MM-dd: 2015-06-02
  • dd-MMM-yy: 02-Jun-15
• Review the long date options (again with June 2, 2015 as an example for all possible combinations).
  • dddd, MMMM dd, yyyy: Tuesday June 02, 2015
  • dddd, MMMM d, yyyy: Tuesday, June 2, 2015
  • MMMM d, yyyy: June 2, 2015
  • ddd, d MMMM, yyyy: Tue June 2, 2015
  • d MMMM, yyyy: June 2, 2015

Method 2 of 2: Change the date formats for specific cells

Step 1. Open the worksheet and highlight all the date fields

If you want to change the date format of a single cell, click that cell only.

• If the dates are aligned in a column: select the entire column by left clicking on the letter above and then right clicking to open an action menu.
• If the dates are in a row, highlight the section or cell you want to change. Then left click on the number to the left of the row to select all cells.

Step 2. Select the Format drop-down menu on the toolbar

This drop-down menu is located in the "Cells" compartment (between "Style" and "Edit") of the "Home" tab.

• Another method is to right-click on the number to the left of a row or the letter at the top of a column. All cells in that row or in that column will be selected and an action menu will open. Select Cell Format in this menu to change the date format in all cells of this column.

Step 3. Select Format Cells from the drop-down menu

You will find this option in the lower part of the menu.

Step 4. Open the Number tab

It's on the left of the Format Cells window, next to Alignment, Police, Border, Filling and Protection. This tab is usually selected by default.

Step 5. Select Date in the Category column

This will allow you to manually change the date format settings.

Step 6. Select the date format you want to use

Highlight your choice and click OK to save the format. Finish by saving the file to make sure you keep the formatting.

Advice

• It is best to apply a single date format to an entire column or row.
• Remember that the date format is essential only for ease of reference. Excel is able to sort cells from oldest to newest (or newest to oldest) regardless of the date format.
• An infinite series of sharps (####) means that someone has tried to enter a date before 1900.
• If that doesn't work, the date may have been saved as text. Someone typed or copied it, but it was not recognized or accepted by Excel. Excel stubbornly refuses to apply any other date format to text such as "mom's birthday" or "02, 02, 2009". While the first example is obvious, the second can be confusing in countries where the month is written before the day and vice versa.
  • All entries that start with an apostrophe will be stored as text, no matter how closely they resemble a date. The apostrophe does not appear in the cell, only in edit mode.
  • The reverse problem also exists. A user might enter 31122009 and think it will be interpreted as December 31, 2009. However, a large number will be inserted directly instead and this number can be interpreted as October 5, 4670. It can be confusing if the format of date does not display the year. Always use a date separator, such as a slash (/).
  • Expand the column to see if the dates have been accepted. By default, the texts are aligned on the left and the dates on the right.
  • This defect may have been corrected. So you can try not to apply any date format. All accepted dates are recorded as numbers up to 40,000 (or more). Try entering your dates as a number and if that doesn't work, it's text. If it works, apply a nicer date format. The values themselves will not be changed by this test.
• Note that by changing the date format, you are applying this setting to a data series. Once a row or column format is set, any new dates added will automatically be displayed in the date format you choose, no matter how you type them.