How to Format Date in Excel Correctly: Step-by-Step Guide

Written By Sophanith Dith
Last Updated May 28, 2026
Applies to Microsoft Excel 365 (Windows only)
Part of the Beginner Learning Path
Module 3 Formatting and Layout
Lesson 2 of 14

Table of Contents

Have you ever typed a date into Excel and watched it change into something unexpected, like 5/1/2026, 1-May-26, or even a strange number? This is a common beginner problem. To format date in Excel correctly, you need to understand that Excel stores dates as values but lets you display them in different styles.

Date formatting matters because dates appear in schedules, invoices, attendance sheets, sales reports, project trackers, and many other workbooks. If your dates are inconsistent or hard to read, your spreadsheet can quickly become confusing.

In this tutorial, you’ll learn how to format dates clearly, change date formats, choose the right Excel date format, and fix simple date display problems without using advanced formulas.

Quick Answer:
To format dates in Excel, select the cells that contain dates, go to the Home tab, open the Number Format drop-down, and choose Short Date, Long Date, or More Number Formats. You can also right-click the cells, choose Format Cells, select either Date or Custom, and apply the date style you need.

Before you start clicking through menus, it helps to understand what you will learn and when to use each option.

Quick Reference

Select the date cells before applying a format.
Use Home tab → Number group → Number Format drop-down for quick date formats.
Use Short Date for compact dates like 5/1/2026.
Use Long Date for readable dates like Friday, May 1, 2026.
Use Format Cells when you need more date options.
Use Custom formats for styles like 01-May-2026 or May 2026.

Once you understand these options, date formatting becomes much easier to control.

What Date Formatting Means in Excel

Date formatting changes how a date looks on the worksheet without changing the actual date value behind it. This is the same basic idea you learned in How to Format Numbers in Excel, but here you are applying it specifically to dates.

For beginners, the most important thing to remember is this: Excel can store a date as a real date value, then display it in many different ways. For example, the same date can appear as 5/1/2026, May 1, 2026, or 01-May-2026.

Date Value vs Date Appearance

Excel separates the actual value from the way it appears. This is why changing the format usually does not change the real date.

For example, if cell A2 contains January 1, 2026, you might display it in several ways:

Date Format	What You See
Short Date	5/1/2026
Long Date	Friday, May 1, 2026
Custom Date	01-May-2026
Month and Year	January 2026

This flexibility is useful because different workbooks need different date styles. A project schedule may need full readable dates, while a sales report may only need short dates to save space.

Beginner Tip:
If a date changes appearance after you type it, Excel is usually trying to recognize it as a date. That is normal, and you can change the display format afterward.

Why Correct Date Formatting Matters

Correct date formatting helps readers understand your worksheet quickly. If one column shows dates as 1/5/2026 and another shows 01-05-26, readers may wonder whether the date means May 1 or January 5.

Consistent formatting is especially important in shared workbooks. It helps avoid mistakes when people sort schedules, review deadlines, read invoices, or compare dates across rows.

Before changing any date format, you need to select the cells you want to format.

How to Format Date in Excel from the Home Tab

The fastest way to format a date in Excel is from the Home tab. This method is best when you only need a common option such as Short Date or Long Date.

The Home tab is beginner-friendly because it keeps the most common formatting tools in one place. You do not need to open advanced settings just to make dates easier to read.

Format Dates Using the Number Format Drop-Down

Use this method when your worksheet already contains dates and you want to quickly change how they appear.

Select the cell or range that contains your dates.
Go to the Home tab.
Find the Number group.
Open the Number Format drop-down.
Choose Short Date or Long Date.

For example, if cells A2:A8 contain order dates, you can select A2:A8 and apply Long Date to make each date easier to read.

Beginner Tip:
You can format one cell, a whole column, or a selected range. For a date column, select only the cells that should contain dates instead of formatting the entire worksheet.

Short Date vs Long Date

Short Date and Long Date are the two easiest choices for beginners. The best option depends on how much detail your worksheet needs.

Option	Example	Best For
Short Date	5/1/2026	Tables, reports, compact lists
Long Date	Friday, May 1, 2026	Schedules, letters, readable summaries

Short Date keeps your worksheet compact. Long Date is easier to read but takes more space, so it may widen your columns.

Beginner Warning:
If you choose Long Date and see ##### in the cell, the column is too narrow. Widen the column to display the full date.

The Home tab is great for quick formatting, but sometimes you need more control over the date style.

How to Change Date Format in Excel Using Format Cells

If the Home tab does not show the date style you want, use the Format Cells dialog box. This gives you more built-in date formats and a clearer preview before you apply the change.

This method is especially useful when you want to choose a date style that matches your report, region, or workbook layout.

Open the Format Cells Dialog Box

There are a few ways to open Format Cells, but right-clicking is the easiest method for most beginners.

Select the cells that contain dates.
Right-click the selected cells.
Choose Format Cells.
In the Category list on the left, select Date.
Choose a date format from the list.
Review the sample preview.
Click OK.

The sample preview is helpful because it shows what your selected date will look like before you apply the format.

For more details about built-in and custom date styles, you can also visit Microsoft’s guide on how to format a date the way you want in Excel

Use the Date Category

The Date category includes several common date styles. Depending on your settings, you may see options such as short numeric dates, dates with month names, or dates with the day of the week.

For beginner worksheets, these formats are usually enough:

Format Style	Example
Numeric date	5/1/2026
Month name date	May 1, 2026
Day and date	Friday, May 1, 2026
Abbreviated month	01-May-26

Use a numeric date when space is limited. Use a month-name date when readability is more important.

Beginner Tip:
If you are not sure which format to use, choose a date style that removes confusion. For example, 01-May-2026 is often clearer than 5/1/2026.

Once you know how to open the date format options, the next step is choosing the best format for your actual workbook.

Choosing the Best Excel Date Format for Your Workbook

Different worksheets need different date styles. The best Excel date format is the one that makes your data easiest to understand for the people reading it.

A date format should be consistent across the same column. Mixing several styles in one date column can make the worksheet look messy and harder to scan.

Good Date Formats for Common Beginner Workbooks

Here are simple recommendations for common beginner situations. Use this table when you are not sure which date style fits your workbook.

Workbook Type	Recommended Format	Why It Works
Simple data list	Short Date	Saves space in rows and columns
Project schedule	Long Date or dd-mmm-yyyy	Easier to read deadlines
Invoice tracker	mm/dd/yyyy or dd-mmm-yyyy	Keeps records consistent
Monthly report	mmmm yyyy	Shows month and year clearly
Attendance sheet	dd-mmm-yyyy	Reduces date confusion

For most beginner workbooks, a format with the month name, such as 01-May-2026, is often the clearest choice.

A clean format makes your worksheet more professional. It also helps later if you sort or filter by date, which is covered in more detail in How to Sort Data in Excel and How to Filter Data in Excel.

Avoid Mixing Date Styles in the Same Column

One of the most common beginner mistakes is formatting different cells in the same date column differently. For example, one cell might show 5/1/2026, another might show May 1, 2026, and another might show 01-May-26.

This does not always break the worksheet, but it makes the data harder to read. A consistent column is easier to scan, print, sort, and explain.

Beginner Warning:
Date formatting should be consistent within the same type of data. If column A is an order date column, use one date style for all order dates.

Sometimes the built-in formats are not exactly what you want. In that case, you can create a custom date format.

How to Change the Format of Date in Excel with Custom Formats

Custom date formats let you control exactly how the date appears. This is useful when the built-in Date category does not include the style you need.

For beginners, custom formats may look a little strange at first because they use date codes such as d, m, and y. Once you learn a few common patterns, they become much easier to use.

Apply a Custom Date Format

Use Custom format when you want a specific style such as 01-May-2026, May 2026, or Friday, May 1.

Select the date cells.
Right-click the selected cells.
Choose Format Cells.
Select Custom.
Click inside the Type box.
Enter a date format code.
Click OK.

For example, if you enter dd-mmm-yyyy, Excel can display a date as 01-May-2026.

Common Custom Date Format Codes

The table below shows beginner-friendly custom date codes. You do not need to memorize all of them, but these are useful for everyday workbooks.

Custom Code	Example Result	Meaning
mm/dd/yyyy	05/01/2026	Month/day/full year
dd-mmm-yyyy	01-May-2026	Day, short month, full year
mmmm d, yyyy	May 1, 2026	Full month name, day, year
dddd, mmmm d, yyyy	Friday, May 1, 2026	Day name plus full date
mmmm yyyy	May 2026	Month and year only

Beginner Tip:
In custom date formats, m represents month and d represents day, and y represents the year. The number of letters changes how much detail Excel shows.

Custom Format Example

Suppose you have a project deadline column in B2:B12. The dates are currently shown as 5/1/2026, but you want them to look clearer for readers.

You can select B2:B12, open Format Cells, choose Custom, and enter:

dd-mmm-yyyy

Your dates will appear like this:

01-May-2026

This style is compact but still clear because the month name removes confusion.

Custom formatting is helpful, but it only works correctly when Excel recognizes your entries as real dates.

Common Date Formatting Problems and How to Fix Them

Sometimes changing the date format does not work as expected. This usually happens when Excel does not recognize the value as a real date, or when the column is too narrow to display the result.

These problems are common for beginners, especially when dates are copied from websites, exported from other systems, or typed inconsistently.

Problem 1: The Date Shows as a Number

Excel stores dates as serial numbers behind the scenes. If a date suddenly appears as a number, the cell may be formatted as General or Number.

To fix it:

Select the cells showing numbers.
Go to the Home tab.
Open the Number Format drop-down.
Choose Short Date or Long Date.

For example, a number like 45413 may appear as a readable date after you apply a date format.

Beginner Tip:
This does not mean your date is gone. In many cases, only the display format changed.

Problem 2: The Cell Shows

When a cell shows #####, the date is usually too wide for the column. This often happens with Long Date formats.

To fix it:

Move your pointer to the right edge of the column letter.
Double-click the column border.
Excel automatically widens the column to fit the content.

You can also drag the column border manually if you want more control.

Problem 3: Excel Does Not Change the Date Format

If the format does not change, the date may be stored as text instead of a real date. This can happen when dates are copied from another source or typed in a way Excel does not recognize.

A simple sign is that the date may align differently from other dates, or sorting may not work correctly. If you are still learning data entry basics, review How to Enter Data in Excel before using more advanced cleanup methods.

For a simple beginner fix, try this:

Click the cell that contains the date.
Press F2 to edit the cell.
Press Enter.
Apply the date format again.

If that does not work, the data may need cleanup in a later lesson.

Beginner Warning:
Do not retype hundreds of dates manually unless the list is very small. Date cleanup tools are better for larger problems, but they belong in a data cleanup lesson.

Problem 4: The Date Looks Like the Wrong Region

A date such as 5/1/2026 can be confusing because some readers may see it as May 1, while others may read it as January 5 depending on their date convention.

To reduce confusion, use a format with the month name, such as:

01-May-2026

This is often easier for beginners and readers because the month is written clearly.

Once your dates are formatted correctly, you can practice with a small worksheet to make the skill feel natural.

Quick Practice

Practice helps you remember where the date formatting tools are and how different formats affect the same date. Use a small sample before applying date formats to an important workbook.

Create this simple practice list in a blank worksheet:

Cell	Value
A1	Order Date
A2	5/1/2026
A3	6/15/2026
A4	7/30/2026

Try these practice tasks:

Select A2:A4 and apply Short Date.
Select A2:A4 and apply Long Date.
Open Format Cells and choose a different Date format.
Apply the custom format dd-mmm-yyyy.
Widen the column if the date does not fit.

The goal is to see that the date value stays the same, but the display changes depending on the format you choose.

After trying the practice steps, review the main points below so you remember which date format option to use in different situations.

Key Takeaways

Date formatting is a simple but important Excel skill. It helps you make dates easier to read, more consistent, and more useful in real-world worksheets.

Date formatting changes how a date appears, not the actual date value.
The Home tab is the fastest place to apply Short Date or Long Date.
The Format Cells dialog box gives you more date format options.
Custom date formats let you create styles like 01-May-2026.
If dates show as numbers, apply a date format.
If dates show as #####, widen the column.
If formatting does not work, the date may be stored as text.
Use one consistent date format in the same column.

These basics will help you prepare cleaner worksheets before moving into other formatting and layout skills.

If you still have questions about how Excel handles dates, the FAQs below answer the most common beginner issues.

Frequently Asked Questions (FAQs)

Beginners often run into the same date-formatting questions because dates can look different depending on how they were entered. These answers focus on practical fixes without going into advanced formulas.

How do I format date in Excel quickly?

Select the date cells, go to the Home tab, open the Number Format drop-down, and choose Short Date or Long Date. This is the fastest method for basic date formatting.

How do I change date format in Excel?

Select the cells, right-click, choose Format Cells, select Date, choose a format from the list, and click OK. This gives you more options than the basic drop-down on the Home tab.

What is the best Excel date format for beginners?

A clear format like dd-mmm-yyyy is often a good beginner choice because it shows the day, month name, and year clearly. For example, 01-May-2026 is less confusing than 5/1/2026.

Why does Excel show my date as a number?

Excel stores dates as serial numbers. If you see a number instead of a date, the cell may be formatted as General or Number. Change the format to Short Date or Long Date to display it as a date.

Why does my date show as ##### in Excel?

The column is probably too narrow. Double-click the right edge of the column header to AutoFit the column width.

Can I create my own date format in Excel?

Yes. Open Format Cells, choose Custom, and enter a custom date code such as dd-mmm-yyyy, mmmm yyyy, or dddd, mmmm d, yyyy.

With those common questions answered, let’s quickly recap why date formatting is such a useful beginner Excel skill.

Conclusion

Learning how to format date correctly helps make your worksheets clearer, cleaner, and easier to understand. When you know how to use Short Date, Long Date, Format Cells, and simple custom date formats, you can control how dates appear without changing the actual date values.

The best way to learn is to practice with a small date list, try different formats, and compare the results.

This lesson is part of the Beginner Learning Path, a structured series designed to help you learn Microsoft Excel step by step from the basics.

← Previous Lesson
How to Format Numbers in Excel: Make Your Data Clear and Easy to Read

Next Lesson →
How to Wrap Text in Excel to Make Long Cell Text Easy to Read

More Lessons in Module 3 – Formatting and Layout

How to Format Numbers in Excel: Make Data Easier to Read

How to Wrap Text in Excel to Make Long Cell Text Easy to Read

How to Align Text in Excel to Make Spreadsheet Easy to Read