In this guide, we’re going to show you how to convert date to text in Excel.
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….
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.
Let's see how the final text will be with the TEXT functions.
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 |
