Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers 2

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")
share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.