Concatenate use on chart title

Ian Betteridge

Active Member
Joined
Mar 25, 2006
Messages
472
Good evening all,
I'm using concatenate to name a chart title. My problem is that when I concatenate two dates I lose the date format. I'm after a title which looks like
06 December 2007 - 13 December 2007
I'm actually getting 39422 - 39429
The cells have been formatted in date format but lose this when concatenated.
Is there a sollution?
Thanks
Ian
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Hi Ian

Did you use Text(Cell, "dd/mmmm/yyyy") in the concatenation?

KR


Dave
 
Upvote 0
The cells that I've concatenated are taken from a formula.
(e.g. =SMALL('1001 HR BOOKED'!H2:H10002,1))
I've tried typing them as shown above in the original question and this then displays ok. How would I get this to work with the formulated cells concatenated.
 
Last edited:
Upvote 0
Yes, Ian - like Dave wrote. Give it a go!

The text box links to a cell. This linked cell needs to look the same as you finally want. Refer help for the text function, it can format numbers into the text you require.

For example, something like in the linked cell
Code:
="This will look "&text(SMALL('1001 HR BOOKED'!H2:H10002,1)),"dd mmmm yyyy")

You can't break anything, just play around with it. Regards, Fazza
 
Upvote 0
Code:
=text(a1,"dd mmmm yyyy")&b1&text(c1,"dd mmmm yyyy")
The ampersand is an alternative construct. No problem using CONCATENATE though.

You can put any text you like in the cell, however you like. The text box should show it.
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,089
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top