Leaving dates in a format that is not globally accepted may lead to misinterpretation in some parts of the world. While the United States is one of the few places that use the mm/dd/yyyy date format and other nations like China and Korea use the yyyy/mm/dd date format, most parts of the world use the dd/mm/yyyy format.
You may need to learn how to change the date format to dd/mm/yyyy in Excel if you think it will suit your audience better. Fortunately, you have come to the right place.
How Can You Change the Date Format In Excel To dd/mm/yyyy?
There are two methods in this post to change the date format to dd/mm/yyyy in Excel as follows:
Method One: Use The Home Tab
Follow these steps to change the date format in excel to dd/mm/yyyy using the Home Tab:
- Open the Microsoft Excel spreadsheet application.
- Go to the spreadsheet that contains the data where you want to change the date format.
- Select the cell containing the date. You can highlight multiple cells in the same place using the mouse or select distant cells by holding the Shift button and clicking each cell simultaneously.
- Click on the Home tab from the panel at the top left corner of the spreadsheet.
- After you click on the Home tab, you will find a section named Numbers. In this section, you will find a small icon with an arrow pointing to the bottom right corner. Select the icon.
- It will display a small window with different options. Select the Custom option from there.
- This option will list different date formats on the right pane of the page, choose the dd/mm/yyyy format from the list.
- If you cannot find the dd/mm/yyyy option in the list, you can type it as "dd/mm/yyyy" in the Type space provided above the options on the right pane.
- Select OK at the bottom of the window to confirm the changes that you have made.
The date format will change to the dd/mm/yyyy format as shown in the image below.
Method Two: Use the Keyboard Shortcut Keys
Follow these steps to change the date format in excel to dd/mm/yyyy using the keyboard:
- Open the Microsoft Excel spreadsheet application on your PC.
- Open the spreadsheet containing the date format that needs to be changed.
- Select the cell you want to change the date format. You can use your mouse to highlight the cells if they are in the same place. If they are separated, hold the Shift button and click on the cells simultaneously.
- Press the Ctrl button + the 1 key on your keyboard if you use Windows OS, or Cmd + 1 on your keyboard if you use Mac OS.
- Click on the Numbers tab in the pop-up window.
- You will find different options in the categories under the Numbers tab. Find the Custom option and click on it.
- The Custom option gives you a list of date formats from which you can click on the dd/mm/yyyy format.
- If it does not appear on the list, you can type "dd/mm/yyyy" in the Type space provided.
- Select OK to save the change you have made.
Why You May Not Be Able To Change Date Format In Excel To dd/mm/yyyy
There are many reasons why you may have issues with changing your date format in Excel to dd/mm/yyyy. Here are a few common reasons:
- Dates are meant to consist of numbers and could be invalid if there are texts.
- The cell's width may be too much.
- The dates may be left-aligned.
- You may have included an apostrophe in the date.
- Excel may have formatted negative numbers as dates.
Conclusion
If you change the date format in Excel to dd/mm/yyyy, you should input the correct data to avoid misinterpretation.
You may also like to read:
How To Add Months To Date In Google Sheets
If Contains Google Sheets Formula (With Examples)
how to export google calendar to google sheets
Export Google Calendar To Excel
How to Add Background Image to Google Calendar Event
