In excel we can use a date in different formats according to our wish by clicking on format cells and selecting the required Date format. This works on most of scenarios. But using formula to set the format of date comes handy at times. So in this Excel tips and tricks guide we will learn to use Excel formula which converts date into desired format.
Excel Formula to Convert Date in desired Format
This way of formatting comes very handy when you are using nested function or output of any function which is date. For example you are creating template and sharing with other. They might not be knowing the formatting. So when extend the formula to some other row or column, they will not get the date in desired format. So if you embed the date format in the excel function itself, then it will be more easier. For formating the date in excel using function, text worksheet function is useful. Example is given for such scenario is given in the end of this post.
Syntax of Text worksheet function
=text(Date, Date Format)
Example:
Let us say, the date is in cell A1, then the formula will be
=TEXT(A1,”dd-mmm-yyyy”) which returns date in 24-Apr-2013
=TEXT(A1,”dd-mm-yyyy”) returns date in 24-04-2013 format
=TEXT(A1,”dd-mmmm-yyyy”)return date as 24-April-2013
=TEXT(A1,”yyyy”) returns 2013
=TEXT(A1,”mmmm”) returns April
Hope you got the concept of using Excel formula to get Date format. This Excel formula to format date is very useful when you are using date inside or along with another Formula. For example, let us say in cell A1 we have “todays date” and in cell B1 we have 24-April-2013. When we concatenate these two cells, the result shows todays date41388. To make date to appear in correct formar we need to enter the formula as mentioned below.
=A1&TEXT(B1,”dd-mmm-yyyy”) which returns todays date24-Apr-2013.
Please let me know if you need further details on the same.
Leave a Reply