TEXT formatting for dates in Excel - Step-by-Step Guide

Introduction

This feature helps you change how dates look in your spreadsheet. You can turn a date into words or numbers in different styles, like showing the month name or just the year. It makes your dates easier to read or match your report style.

When you want to show a date as 'January 1, 2024' instead of 1/1/2024

When you need to display only the month and year from a full date

When creating reports that require dates in a specific text format

When you want to combine a date with other text in one cell

When you want to show the day of the week for a date

Steps

Step 1: Click

- a blank cell where you want the formatted date

The cell is selected and ready for typing

Step 2: Type

- the formula bar

You start entering the formula

💡 Start with =TEXT(

Step 3: Enter

- the formula bar

The formula looks like =TEXT(A1, "mmmm d, yyyy") if your date is in cell A1

💡 Replace A1 with the cell that has your date

Step 4: Press

- Enter key

The cell shows the date in the new text format, for example, January 1, 2024

Step 5: Copy

- the formula cell

You can paste it to other cells to format more dates the same way

Before vs After

Before

Cell A1 shows 1/1/2024 as a date number

After

Cell B1 shows January 1, 2024 as text using =TEXT(A1, "mmmm d, yyyy")

Settings Reference

Date format text

📍 Second argument inside the TEXT formula

Defines how the date will appear as text

Default: "mm/dd/yyyy"

Common Mistakes

Typing the date format without quotes inside the TEXT formula

Excel needs the format as text, so it must be inside double quotes

Always put the date format inside double quotes, like "mmmm d, yyyy"

Using TEXT on a cell that does not contain a valid date

The formula will return wrong or unexpected text

Make sure the cell you reference has a proper date value

Summary

The TEXT function changes how dates look by turning them into text in your chosen style

You must put the date format inside double quotes in the formula

Use this to make dates easier to read or to combine with other text