Convert Date to Text in Excel & Google Sheets

This tutorial will teach you how to convert dates to text in Excel & Google Sheets.

Convert Dates to Text

Excel stores dates as serial numbers, where each whole number represents a unique date.

This is extremely useful as it allows you to perform calculations on the dates, but in some cases you may wish to convert that date to text.

Text Function

To convert dates to text, you can use the TEXT Function:

=TEXT(B3,"M/D/YYYY")

The most important input in the TEXT Function is the display format.  Notice in the previous example we used the “m/d/yyyy” format. Here are other common date display formats:

Notice that you can use the TEXT Function to write out a full date

=TEXT(B3,"mmmm d, yyyy")

You can also display only the month name or year:

or you can use any of those format types to build your desired date format.

Date Formatting

The easiest way to test your date formatting is by opening up the Format Cells Menu and selecting Custom. To open the menu, press CTRL + 1 or click this button:

Navigate to the Custom Form and edit the custom formatting:

Here you can see the result of your formatting in real-time.  Once you have the formatting you need, simply copy and paste it into the TEXT Function.

Type Date as Text

If you’re entering a new date into a cell, simply start the date with an apostrophe (‘). By starting a cell with an apostrophe you tell Excel to trade that cell as text.

This keeps the date exactly as entered and prevents it from being converted to a date stored as a serial number.

Convert Date to Text in Google Sheets

All of the above examples work exactly the same in Google Sheets as in Excel.