How to change the date format in Microsoft Excel to make the program write dates in a certain way

  • 0

How to change the date format in Microsoft Excel to make the program write dates in a certain way

Category : entrepreneur

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

1 spreadhseet

The formatting drop-down menu is located in the “Home” section of the toolbar.
Laura McCamy/Business Insider

3. The menu has two formats for dates: short date and long date. 

3 cell type menu

There are two preset options for formatting dates.
Laura McCamy/Business Insider

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

5 long date

The long date option will write out dates for you.
Laura McCamy/Business Insider

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.

8 4 digit year

You can edit your dates in many different ways.
Laura McCamy/Business Insider

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


About Author

Sammy Singh

Global VC, Founder, and entrepreneur extraordinaire as featured in Inc. Magazine, Bloomberg, and Forbes. Sammy Singh is a graduate of UCLA and Wharton School of Business as well as a former student of Loyola University of Chicago. Sammy is best known as a renowned financial technology global entrepreneur and has founded over 26 different firms across industry and all over the world. He is a venture capitalist,a TV/ Film actor, tax specialist, and marketing solutions strategist. Connect with Sammy Singh on social media below!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.