Change the default Date format in Excel

Change the default Date format in Excel
Last Updated on 12/05/2024
Reading time: 2 minutes

How to change the default Date format in Excel (and also for all the dates of your computer).

  1. First, it's not an Excel setting but a Windows setting

    The default Date format in Excel in managed by Windows

  2. Go to the Region Settings

    There is a trick to open en Region Settings of Windows with a shortcut

How is a date displayed in Excel?

By default, when you insert a date in a cell, the default delimiter between the day, the month, and the year it's the slash. And this, even if you write your date with dashes. Excel automatically replaces the dashes with slashes 🤔

Excel Default display dates

Step-by-Step Instructions to Modify Default Date Format

To customize the default display, you must change a Windows setting.

  1. Open the Regional settings of Windows
  2. You can open it quickly with the command line intl.cpl in the Windows Search box.
Intl cpl to open the regional settings
Intl cpl to open the regional settings
  1. Click on the dropdown button for the short or long date.
Windows Regional Settings
  1. When you press the drop-down button, you display other date formats
  2. But the choices is limited (there is no option to display dates with dashes for example 😕)
Selection of another date format

Create your custom date format for Excel

  1. To Create your custom default date format, click on Additional Settings.
Button Additional Settings
  1. Go to the Date tab
  2. Change the custom date format to dd-MM-yyyy (explanation for the code here)
Custom the default date format

And without restarting Excel, neither your computer, all the dates format in your worksheets has the dash for date delimiter 😉

The default date format presents the dates with dashes

Caution !!!!!!!

  • You don't change the settings of the default date display ONLY for Excel
  • ALL THE DATES of your computer are impacted by this update😲
  • So, take care if you change the date format of your computer. It could generate bad side effects for other applications.
New Date format for Windows

8 Comments

  1. Ash
    05/12/2023 @ 04:32

    I didn't have the dash option only / . However, still helpful. Thanks!

    Reply

    • Frédéric LE GUEN
      05/12/2023 @ 09:01

      It's not a problem. Write your custom code directly in the textbox with the dashes.

      Reply

  2. R Raghavendran
    03/02/2023 @ 15:12

    Thank you very much. Solved an irritating problem.

    Reply

  3. Abi
    26/07/2022 @ 12:05

    This is what I wanted. This is exactly spot on. I got what I wanted, Thanks.

    Reply

  4. Rej
    25/07/2022 @ 20:08

    100% agree, need Excel default not Windows

    Reply

  5. Rob
    30/05/2022 @ 17:37

    I think this post misses the point entirely. This isn't changing the default setting in Excel. This is changing Windows. For example, if I enter the value 5/31 into Excel, it automatically converts it to 31-May. This is totally not useful. I know some people will argue that point, but they're wrong. And here's why. If I create the workbook in 2022, and then in 2025 someone opens this workbook and sees 31-May, they're going to perhaps assume that the date refers to May 31, 2025. This could lead to all kinds of issues. Yes, they COULD select the cell and change the format to mm/dd/yyyy to find out what the actual date is. But most people won't do that.

    Sure, if I type 5/31/22, Excel formats it correctly (5/31/2022). But if I have hundreds of dates to enter and they are scattered in random columns and rows, then physically typing that extra "/22" cost time, creates repetitive motiion that is unnecessary, or requires selecting all those random locations and changing the format. All of this is avoided by allowing us to set a default date code. After all, Excel already has default date codes - we've discussed two of them already. But they need to go one step farther and let us actually have control over what date code forward to use. If I want to set a custom date code format of "yyyy-mmmm-dd hh:mm:ss" then I should be able to set that and not have to muck around setting individual cell formats or typing the entire thing out each time.

    My Windows date format is correct for the region I'm in. What I need to know is how to set a default Date format in Excel that isn't mmm-dd. So the answer to "Change the default date format in excel" is not to change your windows settings. Most people already have the correct date format set for their installation of windows based on their region. After all, the date and time in my task bar always shows a complete date mm/dd/yyyy. So I'm betting that 90% of the people who are searching for this "change the default date format in excel" are not looking to change their windows settings.

    Excel let's you set default fonts, set an office background to "tree rings," and select a "sound scheme" for feedback options. But something that might actually help with data integrity is nowhere to be found....

    Reply

    • Lei
      18/10/2022 @ 06:05

      You got it 100% spot on

      Reply

    • Kenton
      30/10/2022 @ 14:31

      Rob, I have fought the exact same problem for months. All I want is to enter date in any and all spreadsheets and have them default to 01/02/2022, etc. without having to go to the format options every time I create a new worksheet.

      Perhaps there is a way to do this in the registry, but one would assume that that is overkill.

      Have you found a solution?

      Kenton

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Change the default Date format in Excel

Reading time: 2 minutes
Last Updated on 12/05/2024

How to change the default Date format in Excel (and also for all the dates of your computer).

  1. First, it's not an Excel setting but a Windows setting

    The default Date format in Excel in managed by Windows

  2. Go to the Region Settings

    There is a trick to open en Region Settings of Windows with a shortcut

How is a date displayed in Excel?

By default, when you insert a date in a cell, the default delimiter between the day, the month, and the year it's the slash. And this, even if you write your date with dashes. Excel automatically replaces the dashes with slashes 🤔

Excel Default display dates

Step-by-Step Instructions to Modify Default Date Format

To customize the default display, you must change a Windows setting.

  1. Open the Regional settings of Windows
  2. You can open it quickly with the command line intl.cpl in the Windows Search box.
Intl cpl to open the regional settings
Intl cpl to open the regional settings
  1. Click on the dropdown button for the short or long date.
Windows Regional Settings
  1. When you press the drop-down button, you display other date formats
  2. But the choices is limited (there is no option to display dates with dashes for example 😕)
Selection of another date format

Create your custom date format for Excel

  1. To Create your custom default date format, click on Additional Settings.
Button Additional Settings
  1. Go to the Date tab
  2. Change the custom date format to dd-MM-yyyy (explanation for the code here)
Custom the default date format

And without restarting Excel, neither your computer, all the dates format in your worksheets has the dash for date delimiter 😉

The default date format presents the dates with dashes

Caution !!!!!!!

  • You don't change the settings of the default date display ONLY for Excel
  • ALL THE DATES of your computer are impacted by this update😲
  • So, take care if you change the date format of your computer. It could generate bad side effects for other applications.
New Date format for Windows

8 Comments

  1. Ash
    05/12/2023 @ 04:32

    I didn't have the dash option only / . However, still helpful. Thanks!

    Reply

    • Frédéric LE GUEN
      05/12/2023 @ 09:01

      It's not a problem. Write your custom code directly in the textbox with the dashes.

      Reply

  2. R Raghavendran
    03/02/2023 @ 15:12

    Thank you very much. Solved an irritating problem.

    Reply

  3. Abi
    26/07/2022 @ 12:05

    This is what I wanted. This is exactly spot on. I got what I wanted, Thanks.

    Reply

  4. Rej
    25/07/2022 @ 20:08

    100% agree, need Excel default not Windows

    Reply

  5. Rob
    30/05/2022 @ 17:37

    I think this post misses the point entirely. This isn't changing the default setting in Excel. This is changing Windows. For example, if I enter the value 5/31 into Excel, it automatically converts it to 31-May. This is totally not useful. I know some people will argue that point, but they're wrong. And here's why. If I create the workbook in 2022, and then in 2025 someone opens this workbook and sees 31-May, they're going to perhaps assume that the date refers to May 31, 2025. This could lead to all kinds of issues. Yes, they COULD select the cell and change the format to mm/dd/yyyy to find out what the actual date is. But most people won't do that.

    Sure, if I type 5/31/22, Excel formats it correctly (5/31/2022). But if I have hundreds of dates to enter and they are scattered in random columns and rows, then physically typing that extra "/22" cost time, creates repetitive motiion that is unnecessary, or requires selecting all those random locations and changing the format. All of this is avoided by allowing us to set a default date code. After all, Excel already has default date codes - we've discussed two of them already. But they need to go one step farther and let us actually have control over what date code forward to use. If I want to set a custom date code format of "yyyy-mmmm-dd hh:mm:ss" then I should be able to set that and not have to muck around setting individual cell formats or typing the entire thing out each time.

    My Windows date format is correct for the region I'm in. What I need to know is how to set a default Date format in Excel that isn't mmm-dd. So the answer to "Change the default date format in excel" is not to change your windows settings. Most people already have the correct date format set for their installation of windows based on their region. After all, the date and time in my task bar always shows a complete date mm/dd/yyyy. So I'm betting that 90% of the people who are searching for this "change the default date format in excel" are not looking to change their windows settings.

    Excel let's you set default fonts, set an office background to "tree rings," and select a "sound scheme" for feedback options. But something that might actually help with data integrity is nowhere to be found....

    Reply

    • Lei
      18/10/2022 @ 06:05

      You got it 100% spot on

      Reply

    • Kenton
      30/10/2022 @ 14:31

      Rob, I have fought the exact same problem for months. All I want is to enter date in any and all spreadsheets and have them default to 01/02/2022, etc. without having to go to the format options every time I create a new worksheet.

      Perhaps there is a way to do this in the registry, but one would assume that that is overkill.

      Have you found a solution?

      Kenton

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *