CONCATENATE Date formatting

[Chuck B]

The intent of the function below is to create a string like so in a cell:
"Week Of: 01/24/08"

= CONCATENATE("Week Of: ", Mon!A1)

Instead, it's giving me this: "Week Of: 39268"

I believe 39268 is the integer value that Excel uses to represent dates
internally.

I've tried a few different things to make the date come out formatted the
way I want but so far nothing has worked. This is problem some simple thing
but I don't work with Excel formulas much.

Can someone tell me what I'm doing wrong?

Thanks.

 

[Conan Kelly]

Chuck B,

=CONCATENATE("Week Of: ",TEXT(Mon!A1,"mm/dd/yy")

or just

="Week Of: " & TEXT(Mon!A1,"mm/dd/yy")

I don't care for using the CONCATENATE() function when an ampersand will do
the job just fine...too much typing. Look up the TEXT() function in XL
help.

HTH,

Conan

 

[Rick Rothstein \(MVP - VB\)]

=CONCATENATE("Week Of: ",TEXT(Mon!A1,"mm/dd/yy")

or just

="Week Of: " & TEXT(Mon!A1,"mm/dd/yy")

Or even....

=TEXT(Mon!A1,"""Week Of: ""mm/dd/yy")

Rick

 

[Chuck B]

Worked like a charm. Thanks.

Chuck B,

=CONCATENATE("Week Of: ",TEXT(Mon!A1,"mm/dd/yy")

or just

="Week Of: " & TEXT(Mon!A1,"mm/dd/yy")

I don't care for using the CONCATENATE() function when an ampersand will
do the job just fine...too much typing. Look up the TEXT() function in XL
help.

HTH,

Conan

 

[Dave Peterson]

One more:

Put this formula in the cell:
=Mon!A1
and give it a custom format of: "Week Of: " mm/dd/yyyy
The cell's value will still be a date, too!