Results 1 to 10 of 10

Concatenate use on chart title

This is a discussion on Concatenate use on chart title within the Excel Questions forums, part of the Question Forums category; Good evening all, I'm using concatenate to name a chart title. My problem is that when I concatenate two dates ...

  1. #1
    Board Regular
    Join Date
    Mar 2006
    Posts
    472

    Default Concatenate use on chart title

    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

  2. #2
    Board Regular dave3009's Avatar
    Join Date
    Jun 2006
    Location
    Glasgow, Scotland
    Posts
    6,291

    Default Re: Concatenate use on chart title

    Hi Ian

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

    KR


    Dave
    Please state your version of Excel, I use Excel 2007 on Win 7.
    Back up all data before testing VBA codes, and please use [code] tags.
    davehouston.co.uk/

  3. #3
    Board Regular
    Join Date
    Mar 2006
    Posts
    472

    Default Re: Concatenate use on chart title

    I'm referencing a concatenated cell, is this one that needs to be formatted as you've shown above?

  4. #4
    Board Regular
    Join Date
    Mar 2006
    Posts
    472

    Default Re: Concatenate use on chart title

    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 by Ian Betteridge; Dec 17th, 2007 at 05:54 PM.

  5. #5
    Board Regular
    Join Date
    Mar 2006
    Posts
    472

    Default Re: Concatenate use on chart title

    Bump

  6. #6
    Board Regular
    Join Date
    Mar 2006
    Posts
    472

    Default Re: Concatenate use on chart title

    How can I show the concatenated cells in date format?

    ******** ******************** ************************************************************************>
    Microsoft Excel - Book1___Running: 11.0 : OS = Windows Windows 2000
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    E
    1
    05/11/200711/11/2007- 39391-39397
    Sheet1

    [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


    Any help very much appreciated.

  7. #7
    MrExcel MVP
    Join Date
    May 2006
    Location
    Excel 2003, Australia
    Posts
    7,584

    Default Re: Concatenate use on chart title

    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

  8. #8
    MrExcel MVP
    Join Date
    May 2006
    Location
    Excel 2003, Australia
    Posts
    7,584

    Default Re: Concatenate use on chart title

    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.

  9. #9
    Board Regular
    Join Date
    Mar 2006
    Posts
    472

    Default Re: Concatenate use on chart title

    Thanks Fazza
    It's sunk in now eventually.
    Again thanks very much for your time.

  10. #10
    MrExcel MVP
    Join Date
    May 2006
    Location
    Excel 2003, Australia
    Posts
    7,584

    Default Re: Concatenate use on chart title

    You're very welcome, Ian. Keep learning!

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com