How to change date format in Excel? Well, it is an easy thing to do and is very helpful for all your future work. Here is how to do it.
Often while using Excel, we also add dates in our workbooks for many reasons. You might have noticed that sometimes the date format gets changed automatically or we want it in a certain way but it does not appear in that way.
MS Excel uses the same date format as the settings of our computer system. In case you want to change the format of the dates to make it more accessible to users in other parts of the world or even to make your data more compact, here is how you can do it.
How To Change Date Format In Excel
To change the date format, here’s how you can do it.
- Open the Excel spreadsheet that has the dates you want to format.
- Select all the cells with all the dates by clicking and dragging your mouse over them.
- Click the down arrow to the right of the text box in the Number group of the Home tab.
A drop-down menu will appear.
There are two date format options in the menu: Short Date (1/17/2021) and Long Date (Sunday, January 17, 2021).
You can select either, but in case you are looking for some other format, click “More Number Formats” at the bottom of the menu. This opens the Format Cells window. Press Ctrl+1 (Command+1 on Mac) to open this window.
In the Date category of the Format Cells window, select the format you would like to use, in the Type group. In case you want to choose a date format based on how a certain region and language format dates, select an option from the Locale drop-down menu.
Click “OK” upon selecting the format you want to use. The dates in the previously selected cells will automatically change to this new format.
Also Read
How to convert text to number in Excel
How to compare two columns in Excel
How to remove blank rows in Excel
Create and Use Your Own Custom Date Format
Excel offers you several options for formats to choose from but you can also create your own custom date format. Here is how you can do it:
1. Select the cells with the dates you want to format by clicking and dragging your cursor over them.
2. Press Ctrl+1 (Command+1 on Mac) to open the Format Cells window. You’ll automatically be in the Date category.
3. Click “Custom”.
You’ll notice a code in the Type text box.
Edit this code and create your own custom format. Each letter or letter in the code represents a display type.
Here’s what each letter means:
| Code | Displays as |
| m | Months: 1-12 |
| mm | Months: 01-12 |
| mmm | Months: Jan-Dec |
| mmmm | Months: January-December |
| mmmmm | Months: The first letter of the month |
| d | Days: 1-31 |
| dd | Days: 01-31 |
| ddd | Days: Sun-Sat |
| dddd | Days: Sunday-Saturday |
| yy | Years: 00-99 |
| yyyy | Years:1900-9999 |
For example, if you were to enter m/d/yy in the Type box and click “OK,” the dates in the selected cells would be formatted as
You can play around with the other formats and different combinations to find the one that you like that matches your requirements.
Tips for Displaying Dates in Excel
Now that you have learned how to format dates, it is now time to understand how to organize data in Excel for your datasets.
- You can use functions to work with today’s date. In case you want a cell to display the current date always, use the formula =TODAY() and press ENTER.
- The cell should be wide enough to fit the entire date. If the cell isn’t, it will display #####. To make your column big enough to display the date correctly double click on the right border of the column.
- If you import data from an external database, Excel will register the dates as text automatically. The display may look the same as if they were formatted as dates, but Excel will treat the two differently. You can use the DATEVALUE function to convert.
- In case negative numbers appear as dates, change the date system. Excel sometimes will format any negative numbers as a date because of the hyphens. To fix this, select the cells, open the options menu, and select “Advanced.” On that menu, select “Use 1904 date system.”
Why Your Date Format May Not Be Having Issues Changing
There might be many reasons why you are experiencing issues in changing the date format in Excel. Some of the common difficulties are listed below.
- Dates are aligned left
- There could be text in the column, not dates (which are actually numbers).
- A cell is too wide
- An apostrophe is included in the date
- Excel TEXT function is not being used
- Negative numbers are formatted as dates
CONCLUSION
Microsoft Excel is a spreadsheet-based software program developed by Microsoft that uses formulas and functions to organize numbers and data. We can perform a variety of tasks, ranging from simple addition calculations to more intricate formulas such as the VLOOKUP formula.
Easy sheet is a tech blog that features incredible Excel hacks, tips, and tricks. Our goal with the Easy Sheet is to help beginners learn Excel and get answers to all of their questions.
We are the best place to go for anyone who needs assistance with Microsoft Office applications such as Excel, Word, PowerPoint, Publisher, and others.
