Do you need to work on an Excel document that has incorrectly formatted dates? Regardless of why the problem occurred, changing the format in which Microsoft Excel formats dates is very simple. You can choose to change the date format for a specific set of data within an Excel sheet, or change the system date format, so that it is automatically applied to all future Excel documents.
Steps
Method 1 of 2: Change the Default System Date Format
Step 1. Enter the "Date and Time" window
To be able to change the default date format that is automatically applied to all Excel documents, you need to change the one your computer uses to format dates. First go to the menu or "Start" screen. The next step depends on the version of the operating system you are using:
- Windows Vista or Windows 8: go to "Control Panel", then choose "Clock, language and regional options". In Windows 8, as an alternative, you can go to your computer settings and choose the "Date / time and language" option.
- Windows XP: Go to "Control Panel", then select the "Date, Time, Language and Regional Options" icon.
Step 2. Access the geographic area settings
Again, the steps to follow vary depending on the operating system in use.
- Windows 8: from the "Clock and international options" screen, choose the "Change date, time and number format" link located in the "Geographic area" section.
- Windows Vista: Go to the "Regional and Language Options" window, then choose the "Formats" tab.
- Windows XP: Go to the "Regional and Language Options" dialog box, then choose the "Regional Options" tab.
Step 3. Get ready to customize the date format
If you are using a Windows 8 system, make sure the "Formats" tab is open. If you are using Windows Vista, select the "Customize this format" item. If you are using Windows XP, press the "Customize" button.
Step 4. Choose the date format you prefer
You have the option to change the format applied to long and short dates. The "Short Date" format applies to dates entered as follows: "6/12/2015". The "Long Date" format applies to dates entered as follows: December 31, 1999. The formats selected here will apply to all Windows programs, including Excel. At the end of the selection press the "OK" button.
-
Here's what a short date will look like based on your chosen format. June 2, 2015 is used as an example date:
- d / M / yyyy: 2/6/2015
- d / M / yy: 2/6/15
- dd / MM / yy: 02/06/15
- dd / MM / yyyy: 2015-06-02
- yy / MM / dd: 06/15/02
- dd-MM-yyyy: 02-06-2015
- dd-MMM-yy: 02-Jun-15
-
Here's what an extended date will look like based on your chosen format. June 2, 2015 is used as an example date:
- dddd, dd MMMM, yyyy: Friday, June 02, 2015
- dddd, d MMMM, yyyy: Friday, June 2, 2015
- d MMMM, yyyy: June 2, 2015
- dddd, MMMM d, yyyy: Friday, June 2, 2015
- MMMM d, yyyy: June 2, 2015
Method 2 of 2: Change the Date Format of a Specific Data Set
Change Date Formats in Microsoft Excel Step 5 Step 1. Open the Excel document in question, then select the date fields you want to format
If you want to change the date format of a single cell, you just need to select it with the mouse.
- If the dates are entered in a single column, you can format it entirely by selecting the relevant header with the left mouse button and then pressing the right button to bring up the context menu.
- If dates are entered in a row, highlight the cell or set of cells you want to change. If you want to change all the cells of a certain row, select its number with the left mouse button.
Change Date Formats in Microsoft Excel Step 6 Step 2. Access the "Format" drop-down menu of the "Home" tab
You can find it in the "Cells" section (located between the "Styles" and "Edit" group).
Alternatively: select the row number or column header with the right mouse button. This will select all the cells that make up that row or column, and the context menu will be displayed. Choose the "Format Cells" item in the menu to format all the cells belonging to the specified row or column
Change Date Formats in Microsoft Excel Step 7 Step 3. Select the "Format Cells" item from the "Format" drop-down menu
This option is located at the bottom of the menu.
Change Date Formats in Microsoft Excel Step 8 Step 4. Go to the "Number" tab
It is the first tab on the left of the "Format Cells" dialog box. The other tabs present are in order: "Alignment", "Font", "Border", "Fill" and "Protection". Normally the "Number" tab is selected automatically.
Change Date Formats in Microsoft Excel Step 9 Step 5. Choose the "Date" option present in the "Category" panel on the left of the window
This step allows you to manually change the date format used.
Change Date Formats in Microsoft Excel Step 10 Step 6. Choose the date format you prefer
Select it from the "Type" panel, then press the "OK" button to save the changes. When finished, save your document to make sure all the new settings are preserved.
Advice
- A good method is to use only one date format to apply to an entire column or row.
- If a string like "####" appears inside a cell formatted as a date, it means that a date prior to January 1, 1900 has been entered.
- Remember that changes made to the format in which a date is displayed are for reference only to the user. However, Excel is able to sort the data, regardless of the date format chosen.
-
If the methods described in this guide do not work, the dates entered may have been formatted as plain text. This occurs when data is copied or entered manually and is not recognized or accepted by Excel as valid dates. Excel does not agree to format text such as "mother's date of birth" or "12.02.2009" as a date. While the first example is clear to everyone, the second can be a bit confusing for users who are not using a localized English version of Excel. Especially when their versions of Excel (localized for use in Europe) don't accept it as a valid date format.
- Any data in an Excel cell that begins with an apostrophe (') is stored as plain text, regardless of its nature. The apostrophe is not displayed inside the cell, but only in the bar used for data entry.
- There is also the reverse problem. A user could enter the following string 12312009 expecting it to be interpreted as "December 31, 2009". However, in Excel, dates can also be expressed as whole numbers, calculated from the reference date "January 1, 1900". So, in this specific case, Excel will interpret the subject number as "October 5, 4670". This mechanism could be a little confusing, especially when the date format applied does not display the year. To work around this, always use a separator to insert dates, such as the slash (/).
- Try increasing the column width to see if the dates entered have been accepted as such. Data formatted as plain text is aligned to the left by default and dates to the right.
- Warning: this default setting can be changed. So, to understand how the data in question is interpreted, try not to apply any date format. Knowing that all dates accepted as such are stored by Excel as integers, calculated from the baseline date "1/1/1900", try formatting the data as numbers; if the attempt fails, they have been accepted as plain text. If the formatting is successful, you can proceed to apply the date format you prefer. The data in the cells will not be changed using this check procedure.
- Be aware that once you have chosen a specific format to display a date for a given set of cells, it will be used as the default for formatting the data, even if you enter a date in a different format.
