How to Convert Date into a Text in Excel (Formula)

puneet-gogia-excel-champs

- Written by Puneet

As you know, Excel stores a date as a number, and you have the date format on the cell to show it as a date. By converting the date into a text, you can get the day name, day number, month name, and year number.

convert-date-into-text

For this, the best way is to use the TEXT function. This function allows you to extract a part (day, month, and year) from a date as a text. In this tutorial, we will look at a few examples to understand to use this method.

Convert a Date into Text using the TEXT Function

You can use the below steps:

  1. First, enter the TEXT function in a cell.
  2. After that, in the first argument, refer to the original date.
  3. Next, in the third argument, enter the format of the date that you want to get in the result.
  4. In the end, enter closing parentheses and hit enter to get the result.
convert-date-using-text-function
=TEXT(A1,"dd-mmm-yy")

Get Month Name as a Text from a Date

In the TEXT function, you need to refer to the actual date in the first argument, and then in the second argument, you need to specify the format of the date that you want to get in the result.

get-month-name-as-text

In this formula, we have used the format “MMMM”. This format tells the TEXT function to only get the full month name from the date as a text.

=TEXT(A1,"MMMM")

And if you want to get the short month name you just need to change the format from “MMMM” to “MMM”.

get-short-month-name-as-text
=TEXT(A1,"MMM")

Get Day Name as a Text from a Date

And if you want to get the day name as a text from the date, you need to use the same function i.e. TEXT.

get-day-name-as-text

Here you have used the same function with the format to get the full-day name.

=TEXT(A1,"DDDD")

To get the short-day name, you can use the following formula.

=TEXT(A1,"DDD")
get-short-day-name-as-text

Get a Date in a Complete Text Sentence

In the below formula, we have used the TEXT function to create a text sentence to define the date and day name in long sentence form.

="The date is "&TEXT(A1,"DD, MMMM'YY")&" and day is "&TEXT(A1,"DDDD")&"."
get-date-in-complete-text

Get the Excel File

Last Updated: April 30, 2024