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.
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.