- You can change the way that dates are formatted in an Excel spreadsheet with just a few clicks.
- Excel is good at automatically recognising when you enter a date, and formatting things to the date format of your choosing.
- Excel offers a wide variety of format choices for displaying dates.
- Visit Business Insider’s homepage for more stories.
When you enter a date that’s formatted with slashes, Excel will recognise it as a date. For example, Excel will automatically choose the date format for a cell that contains 11/11/19.
You can set Excel to display dates in a variety of ways, no matter how you type in the data.
Check out the products mentioned in this article:
How to change the date format in Microsoft Excel
We’re using a spreadsheet that includes dates of hire for employees of a fictitious company, to show different ways to format dates in Excel.
1. Highlight the date cells you want to format.
2. In the “Home” menu ribbon, there is a field in the middle with a drop-down menu for different cell formats. It should say “Date” by default. Click the arrow to open the drop-down menu.
3. The menu has two formats for dates: short date and long date.
- Short date displays months and days as either single or double digits and years as double digits. For example, January 1, 2019 would be displayed as 1/1/19.
- The long date option adds the day of the week and writes out the month. So 1/1/19 will display as Tuesday, January 1, 2019. The date will display in the format you choose, no matter how you enter it. If you type “1/1/19” into a cell with the long date format, Excel will change it to the expanded date.
4. Excel offers many other date format choices. In the cell format menu, click on “More Number Formats…” at the bottom.
5. In the window that pops up, make sure “Date” is chosen under the “Category” sidebar. You can scroll down the list of date formats in the box to the right. Choose from formats that put the day before the month, drop the year, and more. We chose the last option and the data, which was entered with two-digit years, was converted to display four-digit years.
Pro tip: Never change the format of a cell with a date to General or Text. Excel will turn your date into an unrelated number. For example, 3/3/2017 becomes 42797 when it’s not formatted as a date.