How to Format Dates in Excel - Practical Guide

Learn how to format dates in Excel to display them in your preferred style—from MM/DD/YYYY to custom formats. This essential skill ensures professional data presentation, improves readability across international teams, and prevents date interpretation errors in reports and analyses.

Why This Matters

Proper date formatting ensures consistency, improves data clarity for stakeholders, and prevents calculation errors when dates are misinterpreted across different regions.

Prerequisites

•Basic understanding of Excel spreadsheets and cell selection
•Knowledge of how dates are stored as numbers in Excel
•Familiarity with the Home tab in the ribbon

Step-by-Step Instructions

Select the cells containing dates

Click on the first date cell, then drag to select all cells with dates, or use Ctrl+Click to select multiple non-contiguous cells.

Open the Format Cells dialog

Right-click on the selection and choose 'Format Cells' or press Ctrl+1 to open the dialog box.

Navigate to the Number tab

In the Format Cells dialog, click the 'Number' tab (it's selected by default), then select 'Date' from the Category list on the left.

Choose your date format

Select a predefined format from the list (e.g., 3/14/2021, March 14, 2021, 14-Mar-21) to see a preview of how your dates will display.

Apply the format

Click the 'OK' button to apply the selected date format to all chosen cells.

Alternative Methods

Format using the Home ribbon

Select date cells, go to Home > Number Format dropdown, and pick a date format directly from the menu without opening the Format Cells dialog.

Use custom date formats

In Format Cells > Number > Date category, select 'User-Defined' at the bottom to create custom formats like YYYY-MM-DD or D MMMM YYYY.

Apply with keyboard shortcut

Select cells and press Ctrl+1 to open Format Cells immediately, bypassing the right-click menu for faster formatting.

Tips & Tricks

✓Always check your dates are recognized as dates (not text) by Excel; dates should be right-aligned by default.
✓Use consistent date formats across your entire spreadsheet for professional appearance and to prevent confusion.
✓Consider your audience's regional preferences when choosing a date format (e.g., MM/DD/YYYY for US, DD/MM/YYYY for Europe).

Pro Tips

★Use the 'Date' category's 'Locale' option to automatically apply date formats matching specific countries, avoiding manual adjustments.
★Combine date formatting with conditional formatting to highlight dates—for example, highlight past due dates in red.
★Create a custom format code like [<=9]d/m/yyyy;d/m/yyyy to format single-digit days without leading zeros if needed.

Troubleshooting

Dates display as ##### symbols

The column is too narrow to display the formatted date. Double-click the column border between headers to auto-fit the column width.

Date format doesn't apply after selecting Format Cells

Ensure the cells contain actual dates (not text). If they're text, convert them using the Data > Text to Columns feature first.

Custom date format isn't showing in the predefined list

Go to Format Cells > Number > User-Defined category and type your custom format code (e.g., yyyy-mm-dd) in the 'Type' field.

Related Excel Formulas

TODAY NOW TEXT DATE DATEVALUE

Frequently Asked Questions

Can I format dates to show only the day and month?

Yes, open Format Cells (Ctrl+1), go to Number > Date, and select a format showing only day and month (e.g., 'Mar 14'). For custom formats, use the User-Defined category and enter a code like mm-dd or d mmmm.

What's the difference between 'Date' and 'Time' categories in Format Cells?

The Date category formats how dates display (e.g., 3/14/2021), while the Time category formats how times display (e.g., 2:30 PM). You can also combine both using custom formats.

Why won't my date format change when I apply it?

This typically means Excel isn't recognizing the cell content as a date—it may be stored as text. Try converting it with Data > Text to Columns, or re-enter the date and ensure the cell is formatted as a date beforehand.

How do I create a custom date format that includes the day name (e.g., Monday, March 14, 2021)?

In Format Cells > Number > User-Defined, enter a custom code like dddd, mmmm d, yyyy to display the full day name, month name, day, and year together.