How to Change the Default Date Format in Excel

Regardless of how you format the date, Excel stores it in one format in the backend. When you type dates using hyphens, dashes, or dots, Excel automatically converts them to slashes. This is a major setback especially if you want your date formats to have other delimiters as separators rather than slashes.

In this article, I will show you how to change the Excel default date format.

By changing the date format location

1. Highlight all the cells that will contain Excel dates or that will hold dates 

2. Right-click on only a cell within the highlight

3. Select format cells

4. Under format cells select a date then choose your preferred date format from the list

How to change Excel date format

5. If you do not find a date format that fits you from the list then you need to change the location. 

How to change excel date from slashes to dashes

6. As you can see from the image below you can now type date with hyphens or dashes. 

Excel dates in dashes

How to change Excel date format using VBA Code

If you have a large chunk of data that you want to change from the default date formatting then using VBA code might be your best bet. 

1. To use this code, you can open the Visual Basic for Applications editor in Excel (press Alt + F11),

2. Click on Insert a new Module (Insert > Module), and then paste the following VBA code into the module. 

Sub ChangeDateFormat()

    Dim inputDelimiter As String
    Dim selectedRange As Range
    Dim cell As Range
    ' Prompt user for the delimiter
    inputDelimiter = InputBox("Enter the delimiter for the date format (e.g., / or -)", "Delimiter Input")
    ' Check if the user canceled or entered an empty delimiter
    If inputDelimiter = "" Then
        MsgBox "No delimiter provided. Exiting.", vbExclamation
        Exit Sub
    End If
    ' Set the range where you want to change the date format
    Set selectedRange = Selection ' Change this to your desired range
    ' Loop through each cell in the selected range and change the date format
    For Each cell In selectedRange
        If IsDate(cell.Value) Then
            Dim tempDate As Date
            tempDate = cell.Value
            cell.Value = Format(tempDate, "yyyy/mm/dd") ' Convert date to standard format
            cell.NumberFormat = "@" ' Set cell format to text to avoid auto-formatting
            cell.Replace "/", inputDelimiter ' Replace standard delimiter with the desired one
            cell.NumberFormat = "yyyy" & inputDelimiter & "mm" & inputDelimiter & "dd" ' Apply desired format
        End If
    Next cell
    MsgBox "Date format changed to " & inputDelimiter & " delimiter.", vbInformation

End Sub

3. Highlight the dates that you wish to change formatting for.

4. After that, you can run the macro from Excel (Developer > Macros, select the macro ”ChangeDateFormat”, and click “Run”).

VBA code to change Excel date format

5. Type the delimiter that will separate your dates in the pop-up.

6. Click okay and all the dates will be changed to the format that you have chosen automatically

Similar Read: How to minus Dates in Excel

By changing the computer settings

If you want to change from slashes to hyphens or dots and vice versa to all programs then you have to change the settings in Windows. Here are the steps

1. Search for intl.cpl which is the regional settings for Windows

How to change Excel dates format in computer settings

2. Click on the control panel item to launch its dashboard

3. Under date and time formats click on the drop-down for either short date or long date

4. Now change the date formatting to dshes or dots depending on your preferences.

5. Finally press okay to apply changes

Always remember that this setting affects all other programs including Microsoft Word, PowerPoint, etc.

How to create a custom date format in Excel

The method above has limited pre-set formats. If you want any other format that is not listed then you have to create it from scratch. 

1. Open the intl.cpl control panel

2. Click on Additional settings

3. Another control panel will launch. Now click on the date tab and type the format that you wish to apply under the date formats section. 

How to create custom date formatting

4. Lastly, click on apply then Ok

That’s it, your custom date formatting has been set. All you need is to start Excel and type in dates in your custom date formatting. The dates will be retained as they are without resetting to the default slashes. 

Final Thoughts

Excel sets a default date and time format depending on the computer settings. You can either change the computer settings to use a custom date formatting or change this setting directly in Excel. Changing the computer settings will affect all the Microsoft Programs. However, changing directly in Excel will only affect Excel. You can also use other methods such as VBA code to achieve the same results.

Leave a Reply

Discover more from Excel Wizard

Subscribe now to keep reading and get access to the full archive.

Continue reading