Convert date to Text/String
If you need to convert dates to text (i.e. date to string conversion) , you can use the TEXT function.
The TEXT function can use patterns like "dd/mm/yyyy", "yyyy-mm-dd", etc. to convert a valid date
to a text value. See table below for a list of available tokens.
There are many uses of Text Formula and most of the useful is formating Numbers. for detail Click
Here but here we only discuss about the formating of date in to any text/string as per our need.
Text Formula
Explanation
Dates and times in Excel are stored as serial numbers and converted to human readable values on the fly using number formats. When you enter a date in Excel, you can apply a number format to display that date as you like. In a similar way, the TEXT function allows you to convert a date or time into text in a preferred format. For example, if the date January 9, 2000 is entered in cell A1, you can use TEXT to convert this date into the following text strings as follows:
=TEXT(A1,"dd/mm/yyyy") // "08/11/2022"
=TEXT(A1,"dd-mmm-yy") // "08-Nov-22"
Dates Format Codes
Assuming a date of November 08, 2022, here is a more complete set of formatting codes for date, along with sample output.
| Format Code | Output | Detail |
|---|---|---|
| d | 8 | day display in single number only |
| dd | 08 | day display in 2 numbers |
| ddd | Tue | three-letter abbreviation of the day |
| dddd | Tuesday | Full Name of Day display |
| m | 1 | Month Display in Single Digit |
| mm | 01 | Month Display in Digit with leading Zero |
| mmm | Nov | three-letter abbreviation of the month |
| mmmm | November | Full name of the month in alphabtics |
| yy | 22 | two-digit year |
| yyyy | 2022 | four-digit year |
| h | 1 | Single Digit Hour |
| hh | 01 | two Digit Hour |
| M | 1 | Single-digit minutes |
| MM | 01 | Two-digit minutes with leading zero |
| s | 1 | Single-digit Seconds |
| ss | 01 | Two-digit seconds with leading zero |
| AM/PM | PM | Showing Time in 12 hours Format |
Examples |
||
| mm/dd/yyyy | 11/07/2022 | - |
| mm-dd-yyyy | 11-07-2022 | - |
| ddd, mmm d | Tue, Nov, 8 | - |
| dd/mm/yyyy hh:mm:ss | 09/01/2022 17:15:00 | date with time |
| 09/01/2022 17:15:00 | 01/09/2022 5:15 PM | Date and Time in 12 hours format |
As I mentioned above that you can use the TEXT function to convert dates or any numeric value to a fixed text format. You can explore available formats by navigating to Format Cells (Win: Ctrl + 1, Mac: Cmd + 1) and selecting various format categories in the list to the left.
Source: ExcelJet
0 Comments