CONCATENATE Date formatting

  • Thread starter Thread starter Chuck B
  • Start date Start date
C

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.
 
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
 
=CONCATENATE("Week Of: ",TEXT(Mon!A1,"mm/dd/yy")
or just

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

Or even....

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

Rick
 
Worked like a charm. Thanks.


Conan Kelly said:
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
Click to expand...
 
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!
 
Back
Top