Formatting dates; multiple dates in a single cell

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote