How to pass a formula as one of the arguments to CONCATENATE?

Question

I'm using Microsoft Excel 2010 and I am trying to create a title which will be updated every time the spreadsheet is opened. I want it to say "Forecast from (today's date) until end of 2013".

So far I have the following formula:

=CONCATENATE("Forecast From ",K10," until end of 2013")

In cell K10 I have used =TODAY() so that every time the spreadsheet is opened, it will update that date to be current.

It is not working as I cannot get the ",K10," to be represented as a date within the concatenated string.

Any suggestions on how to fix this or possibly another method I could use to fix this?

Answer 1

Excel stores the date in a serial format. This is what you are seeing when you just concatenate that cell. You have to transform the serial date data into a text format using the Text function.

=Concatenate("Forecast From ",Text(K10,"mm-dd-yyyy")," until end of 2013")

Answer 2

Scott McKinney is correct.

For another option, the same thing is achieved by using the ampersand "&", which is the concatenation operator, like this:

="Forecast From " & TEXT(K10,"mm-dd-yyyy") & " until end of 2013"

You can also move the =TODAY() out of K10 and just rewrite it all as

="Forecast From " & TEXT(TODAY(),"mm-dd-yyyy") & " until end of 2013"

It's really a matter of personal preference.