Have Your Date Your Way: Changing Date Formatting in Microsoft Excel

When you enter dates in Excel, they may not look right at first. That’s okay though! Whenever a number or date doesn’t look the way you want in Excel, you just have to format it. This is simple enough.

Quick Date Format Change

To change date formatting in Excel 2007-2016: Select the cells you want to format. Choose the HOME, then the number format drop-down menu (shown at right) in the NUMBER GROUP. [Select] SHORT DATE or LONG DATE, depending on your preference for a numerical date (ie. 2/27/2012) or a written date (ie. Monday, February 27, 2012).

 More Date Formats

For more date formatting options in Excel 2007 and 2016, complete steps 1 and 2, and [Select] MORE NUMBER FORMATS… from the drop-down menu (or use keyboard shortcut: <Ctrl>-1 (number 1)). Select your preferred date TYPE according to the examples provided, and [Click] OK.

In Excel 97-2003:

1. Select the desired date cell or cells.
2. Choose FORMAT, CELLS…; or press <Ctrl>-1 (digit one).
3. Click the NUMBER tab, if necessary.
4. Select the DATE category, if necessary.
5. Select your preferred TYPE.
6. Click OK.

Excel supports custom date formats but we’ll leave that for another post.

It’s also useful to note that the default short and long date formats follow the settings of your Windows or Mac operating system, allow you to use a region-specific date format by default. If you find you prefer a different date format than your current one, you’ll want to see our post on changing your OS date format in your system settings.

Date Alignment

As we noted in our post on entering dates in Excel, date entries are normally right-aligned, like any numeric value. You can change date cells to use center or left alignment if you prefer. However, it makes it a little harder to detect if they’ve been entered correctly as dates as opposed to being formatted as simple text or numbers. Dates entered correctly have special properties that allow them, for instance, to be sorted according to date. Dates entered as numbers (as opposed to dates) will not be compatible in these situations.

Watch Your Column Widths

In Excel, numeric data cannot exceed the width of a column or else pound symbols or number signs will appear (#####). This could happen if a number is too long, has formatting that takes up a lot of space, or if it’s in a column that’s too narrow. The quick fix is to widen the column.

Sometimes, we want to use a long date format like: Friday, October 26, 2018. You can make the column wider by dragging the boundary on the right side of the column heading until the column is wide enough.

There are, however, instances where we cannot make the column wider. In these cases, we have to merge cells together. To do this:

1. Select two or more adjacent horizontal or vertical cells
2. Choose the HOME tab, [Click] MERGE AND CENTER, found in the ALIGNMENT group. 
   
   

For more on merging cells, see Microsoft’s Excel help page on merging and unmerging cells.

Date cells can be formatted any way you want them to look. And this doesn’t mean that they’ll lose all the functionality that date cells offer. By delving into a rich array of formatting options (rather than entering dates as text) you’ll be able to get the display you want to clearly present dates.

For more on Excel, check out our Beezix Quick Reference Guides, which provide tips, shortcuts and instructions for users of all levels. These provide the information you need in a clear, manageable format for the most popular applications.