fbpx

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

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.

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.

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

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

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

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

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

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.

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

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.

Source: BusinessInsider