Shutterstock It's easy to change the date format in Microsoft Excel.
- You can change the way that dates are formatted in an Excel spreadsheet with just a few clicks.
- Excel is good at automatically recognizing 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 recognize 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.
Here's how to find and change date formats in Excel on a Mac or PC.
Check out the products mentioned in this article:
Microsoft Office (From $149.99 at Best Buy)
MacBook Pro (From $1,299.99 at Best Buy)
Lenovo IdeaPad (From $299.99 at Best Buy)
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.
Laura McCamy/Business Insider The formatting drop-down menu is located in the "Home" section of the toolbar.
3. The menu has two formats for dates: short date and long date.
Laura McCamy/Business Insider There are two preset options for formatting dates.
- 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.
Laura McCamy/Business Insider The long date option will write out dates for you.
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.
Laura McCamy/Business Insider You can edit your dates in many different ways.
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.
Related coverage from How To Do Everything: Tech:
How to remove duplicates in Microsoft Excel to clean up data in individual or multiple columns
How to add a column in Microsoft Excel in 2 different ways
How to hide and unhide columns in Excel to optimize your work in a spreadsheet
How to make a pie chart from your spreadsheet data in Microsoft Excel in 5 easy steps
How to insert multiple rows in Microsoft Excel on your Mac or PC
