In this guide, we’re going to show you how to convert date to text in Excel.

Download Workbook

Date and time in Excel

Excel stores date and time values as numbers. They will be displayed in regular date or time format, because Excel automatically applies number formatting.

Excel assumes that the date 1/1/1900 is “1″ and increases this number by 1 for each day. For example, 12/31/2018 is equal to 43465.

In a similar manner, time values are defined as decimals between 0 and 1. 0 is equal to 12:00 AM, 0.75 is equal to 6:00 PM, or 3:45:20 is approximately 0.15648148148….

As a result, each date/time returning function, such as DATE, TIME, EOMONTH, in Excel returns a number that is displayed as a date.

Converting date (or time) to text in Excel

The keyword here is "number formatting". As mentioned above, Excel can display numbers in regular date and time formatting. What about merging them with regular strings?

This cannot be fixed by applying number formatting to the cell, since it is not a number. The solution is to use number formatting with the TEXT function. The TEXT function simply applies the given number formatting to the specified number.

TEXT(number,format)

Let's see how the final text will be with the TEXT functions.

How to convert date to text in Excel 03

In the above example we used "mm/dd/yyyy" and "h:mm AM/PM" number formatting codes for date and time values, respectively. All these letters represent a digit of the date/time unit. Here is a small cheat sheet:

Letter Meaning Sample
d Day in 1 digit 1, 2, 25
dd Day in 2 digits 01, 02, 25
dddd Day's full name Thursday, Sunday
m (in a date) Month in 1 digit 1, 2, or 12
mmm (in a date) Month name as abbreviated Jan, Feb, Mar
yy Year in 2 digits 98, 00, 21
yyyy Year in 4 digits 1988, 2000, 2021
h Hour in 1 digit 1, 13
hh Hour in 2 digits 01, 13
mm (in a time) Minutes in 2 digits 05, 25

 

Here is the full list of options for the date 4/1/2018 23:59:31 ,

Format Code Description Example (4/1/2018 23:59:31)
yyyy Displays the year as a four-digit number. 2018
yy Displays the year as a two-digit number. 18
m Displays the month as a number without a leading zero. 4
mm Displays the month with a leading zero. 04
mmm Displays the month as text, as an abbreviation. Apr
mmmm Displays the month as text. April
mmmmm Displays the month as a single character A
d Displays the day as a number, without a leading zero. 1
dd Displays the day as a number, with a leading zero. 01
ddd Displays the day as a day of the week, as an abbreviation. Sun
dddd Displays the day as a day of the week, without abbreviation Sunday
h Displays the hour without a leading zero. 23
hh Displays the hour with a leading zero. 23
[h] Displays elapsed time in hours (to be used when the time value exceeds 24 hours). 1036607
m Displays the minute without a leading zero. 4
mm Displays the minute with a leading zero. 04
[m] Displays elapsed time in minutes (to be used when the time value exceeds 60 minutes). 62196479
s Displays the second without a leading zero. 31
ss with a leading zero. 31
[s] Displays elapsed time in seconds (to be used when the time value exceeds 60 seconds). 3731788771
AM/PM Converts to 12-hour time. Displays either AM/am/A/a or PM/pm/P/p depending on the time of day. PM
am/pm pm
A/P P
a/p p

 

You can learn more about number formatting in Excel in the related article: Number Formatting in Excel – All You Need to Know