EXCEL TEXT Function to format Date and Time
Click here to download the file used for below exmaples
TEXT Function: The TEXT function lets you change the way a number appears by applying formatting to it with format codes. It’s useful in situations where you want to display numbers in a more readable format, or you want to combine numbers with text or symbols.
Note:- The result of TEXT functions will be in text format. We can not make any calculations from the results of TEXT functions.
Syntax of the TEXT function
Syntax is TEXT(value,format_text)
1st Argument = value:- A numeric value that you want to be converted into text. This is a compulsory argument.
2nd Argument=format_text:- A text string that defines the formatting that you want to be applied to the supplied value. This is a compulsory argument.
In simple TEXT function says: TEXT(Value you want to format, “Format code you want to apply”)
Date Formats
In the above examples, we can see that D stands for Day, M stands for month and Y stands for Year.
| Date Format Explanation | Result |
| DD | 10 |
| DDD | Sat |
| DDDD | Saturday |
| MM | 08 |
| MMM | Aug |
| MM-MMM | 08-Aug |
| MMMM | August |
| MMM-MM | Aug-08 |
| YY | 19 |
| YYYY | 2019 |
Alternative method: Below you can see another way to use the same formula. Instead of giving reference to the cell we can give within the formula itself.
| Formula used | Result |
| =TEXT( 08/10/2019,”DD/MMM/YYYY”) | 10/Aug/2019 |
In the above examples, we can see the different types of Time formats.
