Multiple dates in a single cell of excel

Microsoft Office 2010 home and student
November 30, 2010 at 03:03:56
Specs: Windows XP
I have a activity to record and there could be three to four dates for start of that activity(Suppose A1) and end of that activity (Suppose B1) {like the activity start on 12-nov-2010, 18-Nov-2010, 25-Nov-2010 and this same activity finishes on 14-Nov, 21-Nov, 26-Nov) So Cell A1 will have all three date i.e 12,18, 25 and cell b1 will have 14, 21 and 26 Nov
I am using ALT + Enter along with comma to enter the multiple dates in one cell.

I want to do some operation and date analysis on these two cell but i understand that the contents of these two cell is not in date format and as per my understanding it is in text format.

Suppose i want to know the time and days between first start and first end (i.e 12-nov and 14-Nov)

What is the process for the same? Is there any direct method for this

See More: Multiple dates in a single cell of excel

November 30, 2010 at 08:20:15
I supoose you could write a formula to extract a "date string" from your text, e.g. using some combination of MID, LEFT, RIGHT, FIND and then use that string inside the DATEVALUE function.

My gut feeling is that it will be cumbersome and troublesome.

Why can't you split the dates into individual cells and use formatting to make them look like one cell?

Using a fill color of White and light gray borders will make three cells look like a single cell with 3 lines but each individual entry will be an actual date.

Posting Tip: Before posting Data or VBA Code, read this How-To.

Report •

November 30, 2010 at 19:11:31
Actuall the sheet is very complex and using the different cells will lead to too much other problems. Can i get some simple solution? using the formula or may be VBA...

Report •

November 30, 2010 at 21:53:58
So let me see if I understand this...

You have a such a complex sheet that splitting these multi-line cells into individual cells so that standard date operations can be performed on them will cause "too much" other problems, so instead you want a "simple" solution for extracting text based dates from longer text strings which can be turned into dates so that mathematical operations can be performed on them. Have I got that right?

OK, is this what you would consider simple?

Given this:

         A                 B
   12-Nov-2010,      14-Nov-2010,
1  18-Nov-2010,      21-Nov-2010, 
   25-Nov-2010       26-Nov-2010

This formula will return 2, since 14-Nov-2010 - 12-Nov-2010 equals 2.


I'll leave it to you to figure out a "simple" solution for any other date analysis on these cells.

Posting Tip: Before posting Data or VBA Code, read this How-To.

Report •
Related Solutions

Ask Question