Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Formatting dates; multiple dates in a single cell

    Hi Loungers.....I need some help formatting dates, especially when entering multiple dates in a single cell. I have columns of cells that are formatted to accept dates. I can enter the date 'manually' (eg: type Sep 7/11) but for the most part, I either enter 9/7 or 9/7/11 and it will display as Sep 7/11............I also use a 'pop-up' calendar that I found on the internet (it installs as an Add-on; you right-click in a cell and the calendar pops up and I select a date)..It is fairly cool......anyway, my current problem is this:

    I sometimes need to enter multiple dates in a single cell; for example, if I am setting up a series of appointments, I will enter Sep 7/11; Sep 8/11; Sep 10/11 etc in a single cell...I use ALT+ENTER to word wrap the dates in the cell..........it would be much quicker if I could enter the dates as 9/7; 9/8; 9/10...or, if need be, 9/7/11; 9/8/11; 9/10/11 and have them display as Sep 7/11; Sep 8/11; Sep 10/11...........I cannot figure out anything using the Custom format for dates.....does anyone have any ideas on how to format those cells so that the dates can be entered as 9/7/11; 9/8/11 etc and will display as Set 7/11; Sep 8/11 etc....I attach a small sample to show you want I mean....thank you for your help
    Attached Files Attached Files

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    I don't believe this can be made to work other than by writing a macro to do it.

    Excel stores dates as numbers, and the various date formats are all just ways of displaying the numbers.

    Once you enter mutltiple dates in the one cell, they just become bits of text that stay the way you enter them.
    Regards
    John



  3. #3
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    Thanks, John...that was kind of where I was at with this, but I just thought that I would ask if it could be done using some custom formatting....and I don't know enough about writing a macro to accomplish this, so I'll likely just stick with manually inputting as I have been doing.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Custom formats work on whole text messages, but is primarily for numbers. Once you add something into the cell that is not a number, a custom number format has little value...

    What you could do to make the entry easier is to use several individual cells to enter the dates, then use a formula to concatenate them to the proper format. For example:
    =TEXT(K6,"mmm d/yy")&";"&CHAR(10)&TEXT(L6,"mmm d/yy")&";"&CHAR(10)&TEXT(M6,"mmm d/yy")

    Steve

  5. #5
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    Thank you, Steve...I had thoight about that, but I am trying to avoid resorting to using several different cells, b/c this may only happen 15% of the time and I don't want to have to add hundreds of extra cells to a worksheet for the few times they will be needed....I think I can get by just manually entering something like Sep 7,8,9,10/2011; Oct 4,5/2011 and make that work for me.

Posting Permissions

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