Tom’s Tutorials For Excel: Make Your Dates Readable When Combined With Text
When combining dates with words in a formula, dates don’t always look like dates in the result.
For example, the above picture shows a date in cell A1.
The formula in cell B1 is ="Today is " &A1
which returns “Today is 40855”. A better formula construction is needed to translate Excel’s serial date number into a recognizable date format.
In this next picture, the TEXT
function is used to format the date for easier readability.
The formula in cell B1 is ="Today is "&TEXT(A1,"MMMM D, YYYY")
.
This next example shows how text and a date can all be in a single formula.
The formula in cell A1 is ="Annual budget for year "&TEXT(TODAY(),"YYYY")
.
Here’s another example, showing how to “set and forget” a formula that will rerurn the date for the last workday in the current month.
The formula in cell A1 (broken into 3 lines for browser compatability) is:
="Last workday of this month: "
&TEXT(WORKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1),-1),
"DDDD, MMMM D, YYYY")
You can nest the above type formulas inside other functions.
In this example, the UPPER
function capitalizes all the month letters.
The formula in cell A1 is ="Today is: "&UPPER(TEXT(TODAY(),"MMMM D, YYYY"))
.
You can add text before and after a date, such as this example shows.
The formula in cell A1 is:
="Printed on "&TEXT(NOW( ),"MMMM D, YYYY at HH:MM AM/PM")&", in full color!"
.
Finally, you can use the “carriage return” ascii character #10 in a formula to show the date on the top line, and the time on the bottom line of a single cell.
The formuls in cell A1 is:
=TEXT(TODAY(),"MMM DD,YYYY")&CHAR(10)&TEXT(NOW(),"hh:mm AM/PM")
.
The cell is formatted for Wrap text: Alt+O+E > Alignment tab > select Wrap text > OK.
Suggestion::MY Only suggestion is to have explanation/use of formula/concept in downloadable sample file which user can store and refer in future.. Thanks
Thanks. Actually, I am working on that, with a comprehensive bundle of workbook examples per theme (lookup, time and date, data tools such as filters, charts, and pivot tables), and so on, which will be for sale, starting in January 2017.