Appending or prepending something is called concatenating in Excel. Concatenating is a big word for combining things in one cell. You could be trying to combine text and data, or two types of data, or two cells of text\data together into one cell. We’ll be discussing how to concatenate text with date data.
How to add text before or after a function in the same cell:
Let’s produce today’s date in an cell. For that, we’ll use the following function:
=TODAY()
Alone, this will produce today’s date in the “mm/dd/yyyy” format. But what if we wanted to add words before or after it. Let’s try to add the words “Lasted Updated: ” before the function. You can add any text you’d like before or after any function as long as you wrap it in quotes and join them with the “&” symbol. Like so:
="Last Updated: "&TODAY()
This will get you close, but unless you’ve seen the way excel formats dates before you’ll be scratching your head when you see the output “Last Updated: 41261″ or whatever the serial assigned to the date is.
Serial Dates
Excel stores dates and times based on a number that can be calculated. Excel calls the numbered assigned to the date a “serial date.” This may help when doing calculations which depend on the date, but this tutorial’s goal is to simply add text before or after a friendly display of the date. We definitely want our date display dynamically, but we’ll need to convert the data to text using the TEXT() function in order to display it properly. The text function needs to first know what you’re going to be converting to text, then it will need to know how to format the text. So let’s tell excel to show today’s date in mm/dd/yyyy format, and prepend “Last Updated: ” before it. The field will look like this:
="Last Updated: "&TEXT(TODAY(),"mm/dd/yyyy")
This will output exactly what you are looking for. From the inside out: We took today’s serial date, converted it to text and then formatted that text as mm/dd/yyyy and then prepended “Last Updated: ” to it.
- Text Function - http://office.microsoft.com/en-us/excel-help/text-function-HP010062580.aspx
- Today Function - http://office.microsoft.com/en-us/excel-help/today-function-HP010062297.aspx
Let me know your experiences with this.
Cheers!
Thanks for the info on attaching text to formula.
I altered it to give the time also:
=”Last Updated: “&TEXT(NOW(),”dd/mmm/yyyy h:mm”)
but find it does not give the last time updated, rather it gives the last time opened or saved.
Larry I’m sorry. Perhaps my example was misleading. The tutorial will give you today’s date. It will not be able to show you the modified date of the file. A better user of static text to append would be “Today’s date is: ” – I apologize for the confusion. Perhaps there’s a function that can use file data like that. I’ll do some searching.
[...] my last post about how to append or prepend text to data in a cell, Larry asked how one might be able to display the modified date of an excel file. This tutorial [...]
Larry,
Here’s a link to a new tutorial explaining how to display the modified date in a cell using a macro.
http://danblee.com/display-modified-date-of-excel-file/
Let me know how it goes.