How to Change the Date Format in Excel On PC or Mac

Excel is able to recognise a date that has been entered and formatted with slashes because the programme is aware of the kind of data that it is. For instance, if one of your cells contains the date “11/11/19,” Excel will choose the correct date format for that cell on its own. Excel can be set up to display dates in a variety of formats, and you have the option of selecting the one that best suits your needs. This is true regardless of how the data is entered. By adhering to these instructions, users of Excel on a Mac or a PC will be able to change the format of the date displayed in the program. So let’s get started:

Read Also: How to Turn Gridlines On and Off in Microsoft Excel

How to Change the Date Format in Excel On PC or Mac

For the purpose of demonstrating the various methods for formatting dates in Excel, we’re making use of a spreadsheet that contains the hire dates of people working for a made-up corporation.

1. Select the cells that contain the dates that you want to format.

2. There is a field in the middle of the “Home” menu ribbon that contains a drop-down menu with various options for the format of the cells. The default text there ought to be “Date.” To reveal the submenu, you’ll need to click the arrow.

3. The date options on the menu are presented in two different formats: a short date and a lengthy date.

• The months and days of the short date are represented by single or double digits, whereas the years are represented by double digits. For instance, the first of January in 2019 might be represented as 1/1/19.
• When you select the lengthy date option, the day of the week and the month will both be written out. Therefore, the date that will appear is January 1, 2019, on Tuesday. No matter how you enter the date, it will always display in the format that you have selected. Excel will automatically convert the date to the expanded format if you write “1/1/19” into a cell that is formatted with the long date.

4. Excel provides a wide variety of additional date format options. Click “More Number Formats…” at the bottom of the menu that appears when you select the format for a cell.

5. In the new window that has shown, make sure that the “Date” option is selected in the sidebar under “Category.” You can browse through the various date formats by scrolling down the box on the right. You have the option of using forms that reverse the order of the day and month, as well as those that omit the year. We went with the third and final choice, and as a result, the data, which had been inputted using two-digit years, was changed so that it displays using four-digit years.

A helpful hint: you should never convert a cell that contains a date to the General or Text format. Excel will convert the date you enter into a number that has no bearing on it. When it is not presented as a date, for instance, March 3, 2017, becomes the number 42797.

FAQs

Why won’t Excel change the date format?

Because Excel organises valid dates in the form of numbers, you will need to convert the dates to numbers in order to sort them or change the format of their display. When this happens, you should copy a cell that is blank, then select the cells that contain the dates, and use the Paste Special > Add command to alter the copied dates to the correct dates.

Why is Excel changing date format?

The spreadsheet tool Microsoft Excel has preprogrammed features that make it simpler to insert dates. For example, 12/2 will now be written as 2-Dec. When you type something that you don’t want changed to a date, this can be a very annoying experience. Sadly, there is no way to disable this feature at this time.

How do I convert mm/dd/yyyy to text?

Use the Text to Column format to convert the date to text.

Navigate to the Data menu, then Data Tools, and finally Text to Column. This would result in the dates being instantly converted into text format. Please take note that the dates in the two columns are presented in slightly different formats. The result is written in the format dd-mm-yyyy, even though the original format was dd-mmm-yyyy.