Change Date Formats in Microsoft Excel

Revision as of 09:51, 25 February 2016 by Kipkis (Kipkis | contribs) (importing article from wikihow)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)

Have you inherited a document with the dates in the wrong format? Maybe you were the one who made the mistake, or you simply have decided to go a different route. Whatever the reason, you can quickly and easily change the date format in Microsoft Excel. You can choose to change the date format for a specific set of data within an Excel sheet, or you can change the standard date format for your entire computer in order to apply that format to all future Excel sheets.

Steps

Changing the Standard Date Format

  1. Navigate to the time and date settings. In order to change the standard date format for any new Excel sheet, you will need to change the overarching date format for you computer. First, click the Start button. The next step will depend on which operating system you are using:[1]
    • If you are using Windows Vista or Windows 8: Open the Control Panel. Then, click "Clock, Language, and Region." Alternately, in Windows 8, open the Settings folder and select "Time & language".
    • If you are using Windows XP: Open the Control Panel. Then, click "Date, Time, Language, and Regional Options."
  2. Navigate to the Regional options. Again, the navigational steps vary from operating system to operating system.
    • If you are using Windows 8: In the Clock, Language, and Region folder, select "Change date, time, or number formats" from beneath the "Region" heading.
    • If you are using Windows Vista: Open the Regional and Language Options dialog box. Then, select the Formats tab.
    • If you are using Windows XP: Open the Regional and Language Options dialog box. Then, select the Regional Options tab.
  3. Prepare to customize the format. If you are using Windows 8: Make sure that the Formats tab is open. If you are using Windows Vista: Click Customize this format. If you are using Windows XP: Click Customize.[2]
  4. Choose a date format. You will have options for the short date and the long date. The short date refers to the abbreviated version: e.g. 6/12/2015. The long date refers to the wordier form: e.g. December 31, 1999. The formats that you select here will be standardized across all Windows applications, including Excel. Click "OK" to apply your choices.
    • Review the short date options. June 2, 2015 is used as an example.
      • M/d/yyyy: 6/2/2015
      • M/d/yy: 6/2/15
      • MM/dd/yy: 06/02/15
      • MM/dd/yyyy: 06/02/2015
      • yy/MM/dd: 15/06/02
      • yyyy-MM-dd: 2015-06-02
      • dd-MMM-yy: 02-Jun-15
    • Review the long date options. June 2, 2015 is used as an example.
      • dddd, MMMM dd, yyyy: Friday, June 02, 2015
      • dddd, MMMM d, yyyy: Friday, June 2, 2015
      • MMMM d, yyyy: June 2, 2015
      • dddd, d MMMM, yyyy: Friday, 2 June, 2015
      • d MMMM, yyyy: 2 June, 2015

Changing Date Formats for Specific Sets

  1. Open the spreadsheet and highlight all the relevant date fields. If you only want to change the date format for one cell: simply click on that cell.[3]
    • If the dates are aligned in a column: select and format the entire column by left-clicking on the letter at the top of the column. Then, right-click to bring up an action menu.
    • If the dates are laid out in a row: highlight the section or cell that you want to change. Then, left-click on the number at the far-left of the row to select all of the cells.
  2. Select the drop-down "Format" menu from the toolbar. Find the drop-down menu in the "Cells" compartment (between "Styles" and "Editing") while you are in the "Home" tab.
    • Alternately: right-click on the number on the far-left of a row or the letter at the top of a given column. This will select all of the cells in that row or column, and it will bring up an action menu. Select "Format Cells" from that menu to format the date for all of the cells in that column.[4]
  3. Select "Format Cells" from the drop-down menu. Look for this option at the bottom of the menu.
  4. Navigate to the "Number" tab. Find this on the tab on the far-top-left of the "Format Cells" window, next to "Alignment," "Font," "Border," "Fill," and "Protection." "Number" is usually the default.
  5. Select "Date" from the "Category" column at the left of the screen. This will allow you to manually reformat format the date settings.
  6. Select the date format you'd like. Highlight that choice, and click "OK" to save the format. Then, save the file to ensure that your formatting is preserved

Video

Tips

  • It's best practice to apply only one date format for the entire column or row.
  • Remember that the date format is only important for ease of reference. Excel will be able to sort from oldest to newest (or newest to oldest) regardless of the date format.
  • An infinite string of pound signs (####) indicate that someone has tried to enter a date before 1900.[5]
  • If you can't get this to work, the date may have been saved as a text. Someone has entered or copied it, but it has not been recognized or accepted as a date by Excel. Excel stubbornly refuses to apply another date format to a text like "mom's birthday", or "12.02.2009". While the first example is obvious, the second may confuse lots of people outside of the USA. Especially when their local version of Excel does accept it.
    • All entries that start with an apostrophe will be stored as a text, no matter how much they try looking like dates. The apostrophe doesn't show up in the cell, only in the cell editor.
    • The reverse problem also exists. One may enter 12312009 and expect to enter December the 31st of 2009. However, a huge number will be written directly to a placeholder for dates, and this number is interpreted as the 5th of October 4670. This may be confusing when the date format doesn't show the year. Always use a date separator, such as a slash ( / ).
    • Try widening the column to test whether the dates have been accepted. Texts are left-aligned, while dates are right-aligned by default.
    • This default may have been changed. Thus, one may also try to apply no date format at all. All accepted dates are stored as numbers, with a value of about 40,000 in 2009. Try to format it as a number. When this fails, it's a text. When you’re done, apply a nicer date format. The values themselves will not be changed by this test.
  • Know that typing the date format is setting the date format for a given set of data. Once you have set the format for a given row or column, any new dates that you add will be automatically saved in the default date format you chose – regardless of how you type them.

Related Articles

Sources and Citations

  1. ↑ https://support.office.com/en-us/article/Change-the-date-system-format-or-two-digit-year-interpretation-aaa2159b-4ae8-4651-8bce-d4707bc9fb9f#bmchange_the_default_date_format_to_dis
  2. ↑ http://excelribbon.tips.net/T011575_Setting_a_Default_Date_Format.html
  3. ↑ https://www.ablebits.com/office-addins-blog/2015/03/11/change-date-format-excel/
  4. ↑ http://www.extendoffice.com/documents/excel/585-excel-change-american-date-format.html
  5. ↑ https://support.office.com/en-us/article/Format-a-date-the-way-you-want-8e10019e-d5d8-47a1-ba95-db95123d273e
Retrieved from "https://kipkis.com/index.php?title=Change_Date_Formats_in_Microsoft_Excel&oldid=14644"