Tom’s Tutorials For Excel: Make Your Dates Readable When Combined With Text
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.
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: "
"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")).
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.