Tom’s Tutorials For Excel: Make Dates look Familiar When Combined With Words In A Formula

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.