spreadsheet formula references named range date field

[beckyinDC]

I want to have a cell in my spreadsheet do this...

=CONCATENATE("Executive Summary of SAT for ", DSRReportDate)

and I also tried

="Executive Summary of SAT for " & DSRReportDate

problem: Both are resulting in the DSRReportDate being formatted as a number 40707...should be 6/13/2011

...can I force it to be shown as a date?

the named range references a cell that has a calculation based upon other date fields (it goes with a user-defined date if one is specified, otherwise references a field with calculation Today()). All 3 fields (DSRReportDate, user-definedReportDate and TODAY are in cells formatted as dates.

any help appreciated...do I have to use VBA?

 

[Akashwani]

Hi BeckyinDC,

Does this work....

=CONCATENATE("Executive Summary of SAT for ", TEXT(DSRReportDate,"MM/DD/YYYY"))

Or this....

="Executive Summary of SAT for "&TEXT(DSRReportDate,"MM/DD/YYYY")

I hope that works for you.

Ak

 

[beckyinDC]

Thank you so much - that did the trick. I apologize if it was a little obvious, but I was hunting in the wrong direction so this helped me a bunch.

 

[Akashwani]

Hi Becky,

No problem, thanks for the feedback, I'm pleased it worked for you.

Ak