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:

LetterMeaningSample
dDay in 1 digit1, 2, 25
ddDay in 2 digits01, 02, 25
ddddDay's full nameThursday, Sunday
m (in a date)Month in 1 digit1, 2, or 12
mmm (in a date)Month name as abbreviatedJan, Feb, Mar
yyYear in 2 digits98, 00, 21
yyyyYear in 4 digits1988, 2000, 2021
hHour in 1 digit1, 13
hhHour in 2 digits01, 13
mm (in a time)Minutes in 2 digits05, 25

 

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

Format CodeDescriptionExample (4/1/2018 23:59:31)
yyyyDisplays the year as a four-digit number.2018
yyDisplays the year as a two-digit number.18
mDisplays the month as a number without a leading zero.4
mmDisplays the month with a leading zero.04
mmmDisplays the month as text, as an abbreviation.Apr
mmmmDisplays the month as text.April
mmmmmDisplays the month as a single characterA
dDisplays the day as a number, without a leading zero.1
ddDisplays the day as a number, with a leading zero.01
dddDisplays the day as a day of the week, as an abbreviation.Sun
ddddDisplays the day as a day of the week, without abbreviationSunday
hDisplays the hour without a leading zero.23
hhDisplays the hour with a leading zero.23
[h]Displays elapsed time in hours (to be used when the time value exceeds 24 hours).1036607
mDisplays the minute without a leading zero.4
mmDisplays the minute with a leading zero.04
[m]Displays elapsed time in minutes (to be used when the time value exceeds 60 minutes).62196479
sDisplays the second without a leading zero.31
sswith a leading zero.31
[s]Displays elapsed time in seconds (to be used when the time value exceeds 60 seconds).3731788771
AM/PMConverts to 12-hour time. Displays either AM/am/A/a or PM/pm/P/p depending on the time of day.PM
am/pmpm
A/PP
a/pp

 

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