How to convert a date to text (dd/mm/yyyy or in another format)? Many of you are wondering the same question so today, Ted Jordan teaches you how to do it with TEXT function.
The TEXT function for dates is mainly used to convert dates to texts or to show the name of the day of a date.
Today, we are converting dates to text values in the following formats: dd/mm/yyyy, dd/mm/yy and mm/dd/yyyy.
You’ll see: it’s quick and easy!
Are you ready for this free Excel tutorial?
How to convert a date to text in Excel?
Convert dates to text values in Excel by using the TEXT function. If you just change the format of a cell containing a date to text, using the Format tool, you will probably end up with a serial number in MS Excel.
Indeed, dates are registered as serial numbers in Excel!
Serial numbers are sequential numbers representing dates. By default, the serial number 1 corresponds to 1 January 1900. The serial number 1,000 will correspond to the 1,000th day after 1 January 1900: 26 September 1902.
Convert date to text in Excel – dd/mm/yyyy
Change a date to text in DD/MM/YYYY format by using the Excel formula =TEXT(value,”dd/mm/yyyy”).
value can be a cell reference with a date in the cell (B5 for example) or a date (“10/02/2024” for example).
Important: if you enter a date as the value argument, you have to insert it within quotation marks (“”). For example, =TEXT(“12/1/2023″,”dd/mm/yyyy”) will result in 12/01/2023 in text format.
In Excel, text is on the left of the cell while numbers or dates are on the right.
If you want to use another format than dd/mm/yyyy, check the examples below!
Convert date to text in Excel – dd/mm/yy
Convert a date to text as DD/MM/YY by using the Excel formula =TEXT(value,”dd/mm/yy”).
value can be a cell reference with a date in the cell (D5 for example) or a date (“14/2/2024” for example).
Important: if you enter a date as the value argument, you have to insert it within quotation marks (“”). For example, =TEXT(“12/01/2023″,”dd/mm/yy”) will result in 12/01/23 in text format.
In Excel, text appears on the left of the cell while numbers or dates are on the right.
If you are in the US, you may use MM/DD/YYYY format for dates. In this case, check the example below to change date to text as MM/DD/YYYY.
Convert date to text in Excel – mm/dd/yyyy
Change a date to text as MM/DD/YYYY by using the Excel formula =TEXT(value,”mm/dd/yyyy”).
value can be a cell reference with a date in the cell (D5 for example) or a date (“14/02/2024” for example).
Important: if you enter a date as the value argument, you have to insert it within quotation marks (“”). For example, =TEXT(“12/01/2023″,”mm/dd/yyyy”) will result in 01/12/2023 in text format.
In Excel, text is on the left of the cell while numbers or dates are on the right.
Now, you know how to convert dates to text values in Excel in different formats. If you need to extract a day from a date, use the same logic: the formula will be =TEXT(value,”dd”).
If you wish to change a date to a week day, as text, in Excel, check our tutorial about WEEKDAY. You will then be able to convert a date to days of the week, as per below.
Learn more Excel formulas by joining our Professional MS Excel Course now! Click here to join our Online Course.
