HomeWinBuzzer TipsHow to Change the Date Format in Excel

How to Change the Date Format in Excel

We show you how to change the date format in Excel to other local formats and using a custom format.

-

Dates and times are not just mere values in Excel – they are crucial data points that drive analytics, reporting, and day-to-day data entry. Excel acknowledges this by providing a plethora of options to format and customize these values. Whether you are preparing a report, analyzing trends, or simply organizing your data, the way you present date and time information can significantly impact the clarity and effectiveness of your data.

However, users often find themselves working with data that does not align with their regional settings or specific presentation requirements. This is where the ability to change date formats in Excel becomes invaluable. From simple adjustments like switching between U.S. and European date formats to creating custom formats tailored to specific needs.

How to Change the Date Format in Excel Using the Date Format List

This method is ideal for users who are looking for a quick and straightforward way to format dates. The Date Format List in Excel provides a variety of common date formats that can be applied with just a few clicks, whether you need to display dates in a different style for reporting purposes or align them with regional standards.

  1. Select the cells with dates and open the “Format Cells” dialog

    Select cells:

    Click on the cell or range of cells that contain the dates you wish to format. You can select a single cell, drag to select a range, or use Ctrl+Click to select multiple non-adjacent cells.
     
    Open the “Format Cells” dialog: Right-click on the selected cells and choose “Format Cells” from the context menu. Alternatively, press “CTRL + 1” as a shortcut.
     
    Windows 11 - Excel - Select Cells - Ctrl+1
  2. Select the date type for the selected Excel cells
     
    In the “Number” tab, choose “Date” as “Category” and select the “Type” you want to use. You will see a list of different date formats, that includes a variety of common date formats with different combinations of day, month, and year. Scroll through this list to preview how your selected data will look in various formats.
     
    Windows 11 - Excel - Format Cells - Number - Date Type - Accept
  3. Review the changed date format in your spreadsheet
     
    After applying the new date format, review your data to ensure the dates appear as expected. If needed, you can always go back to the Format Cells dialog box to choose a different format or adjust the formatting further.
     
    Windows 11 - Excel - Format Cells - Number - Date Type - Accept - Result

How to Change the Date Format in Excel Using a Custom Format

Excel allows users to create their own custom date formats, enabling precise control over how dates are displayed. Whether it's a unique combination of day, month, and year, or a specific format that aligns with your business requirements, custom formatting opens up new possibilities.

  1. Select the cells with dates and open the “Format Cells” dialog
     
    Select cells:
    Click on the cell or range of cells that contain the dates you wish to format. You can select a single cell, drag to select a range, or use Ctrl+Click to select multiple non-adjacent cells.
     
    Open the “Format Cells” dialog: Right-click on the selected cells and choose “Format Cells” from the context menu. Alternatively, press “CTRL + 1” as a shortcut.
     
    Windows 11 - Excel - Select Cells - Ctrl+1
  2. Apply a custom category for dates in the selected Excel cells
     
    In the “Number” tab, choose “Custom” as “Category” and enter the custom “Type” you want to use, like d-mmm-yy for a format that shows 06-Mar-99.
     
    Windows 11 - Excel - Format Cells - Number - Custom - Accept
  3. Review the changed date format in your spreadsheet
     
    After applying the new date format, review your data to ensure the dates appear as expected. If needed, you can always go back to the Format Cells dialog box to choose a different format or adjust the formatting further.
     
    Windows 11 - Excel - Format Cells - Number - Custom - Accept - Result

How to Change the Date Format in Excel Converting to Other Local Date Formats

This method is particularly useful when working with data that originates from different regions or systems, where date formats can vary significantly. Changing the date format in Excel by converting to other date formats involves a few specific steps. This process is particularly useful when you need to adjust the format of a date to meet specific requirements, such as changing from American (MM/DD/YYYY) to European (DD/MM/YYYY) formats, or vice versa. Here's how you do it:

  1. Select the cells with dates and open the “Format Cells” dialog
     
    Select cells:
    Click on the cell or range of cells that contain the dates you wish to format. You can select a single cell, drag to select a range, or use Ctrl+Click to select multiple non-adjacent cells.
     
    Open the “Format Cells” dialog: Right-click on the selected cells and choose “Format Cells” from the context menu. Alternatively, press “CTRL + 1” as a shortcut.
     
    Windows 11 - Excel - Select Cells - Ctrl+1
  2. Select the locale and date type for the selected Excel cells
     
    In the “Number” tab, choose “Date” as “Category” and select the “Locale (location):”. Then choose the desired date “Type”. You will see a list of different local date formats for this locale to choose from, with different combinations of day, month, and year. Scroll through this list to preview how your selected data will look in various formats.
     
    Windows 11 - Excel - Format Cells - Number - Date - Location - Accept

  3. Review the changed date format in your spreadsheet
     
    After applying the new date format, review your data to ensure the dates appear as expected. If needed, you can always go back to the Format Cells dialog box to choose a different format or adjust the formatting further.
     
    Windows 11 - Excel - Format Cells - Number - Custom - Code - Accept - Result

What to Do if Excel Is Showing the Wrong Dates After Changing the Date Format

When changing the date format in Excel using the Date Format List, if the dates are not showing up correctly, it could be due to several reasons:

  • Text Format Instead of Date Format: If the cells containing the dates are formatted as ‘Text', Excel will not recognize them as dates. Consequently, when you try to apply a date format, it will not work correctly. Ensure that the cells are not formatted as text before applying a date format.

  • Incorrect Date Entry: Excel recognizes dates in a specific format (usually based on the system's regional settings). If dates are entered in a format that Excel does not recognize as a date, it will treat them as text, and the formatting will not be applied correctly. For instance, entering '13/25/2021′ will not be recognized as a valid date because there are no 25 months.

  • Regional Settings Mismatch: Excel's interpretation of dates depends on your system's regional settings. For example, in the US, the standard format is ‘month/day/year', while in many European countries, it's ‘day/month/year'. If your data is in a format different from your system's settings, Excel may misinterpret the dates, leading to incorrect display after formatting.

  • Leading Zeros Omitted: Sometimes, dates entered without leading zeros (like 1/2/2023 instead of 01/02/2023) can cause inconsistencies in date formatting, especially in custom formats.

  • Ambiguous Dates: Dates like '01/02/2023′ can be ambiguous – it could be January 2nd or February 1st, depending on the regional format. Excel might interpret such dates differently than intended.

  • Data Corruption or Excel Bugs: In rare cases, issues with the Excel file itself or bugs in Excel can cause formatting problems.

To troubleshoot, first, check the cell format and ensure it's set to ‘Date' or a general format that Excel can interpret as a date. Then, verify the date entries themselves for any inconsistencies or errors. If regional settings might be an issue, consider standardizing date formats in your data to match your system's settings or vice versa. If the problem persists, it could be due to a more complex issue with Excel or the specific workbook.

Extra: How to Insert a Checkbox in Excel

A checkbox is a simple control that I'm sure everybody will have encountered online, often as part of a cookie dialog or where you'll tell a site to remember you being logged in. Checkboxes in Excel are much the same thing, but you may not be aware of how useful they can be. In our other guide, we show you how to add check boxes in Excel, demonstrate how they function as part of a spreadsheet, and show how they can be used to build a To-Do list.
 
Featured - How to insert a checkbox in Excel

Extra: How to Create, Use and Edit a Drop Down List in Excel

Creating a drop down list in Excel is an essential skill for anyone who regularly works with . This feature enhances data entry efficiency, ensures consistency, and minimizes errors, making it invaluable in various applications such as data analysis, financial reporting, and inventory management. In our other guide we show you how to use drop down lists in Excel – creating, applying, updating and extending them.
 
Featured - How to add drop down list in Excel

Markus Kasanmascheff
Markus Kasanmascheff
Markus is the founder of WinBuzzer and has been playing with Windows and technology for more than 25 years. He is holding a Master´s degree in International Economics and previously worked as Lead Windows Expert for Softonic.com.

Recent News