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
5. If you do not find a date format that fits you from the list then you need to change the location.
6. As you can see from the image below you can now type date with hyphens or 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”).
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
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.
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.