Excel Help - Multiple dates in cells

Factual Questions
1

Hey, all. My hours of training in Google-Fu has failed me. I have a hopefully simple Excel question.

I have a workbook set up that will give the day of the week in column B based on the date given in column A. (if A1 is 4/8/08, B1 says Tuesday).

My problem is that I want to put in multiple dates into column A, like “4/08/08 & 4/09/08” and have it show up in column B as “Tuesday & Wednesday”.

I can take any variation of formatting, but it has to stay as dates in the same cell.

Many thanks, and good luck.

2

My vote is going for no-can-do, because at some level Excel stores its numbers as a single value, then displays that value in your chosen number format (like dates).

I think you will need multiple columns for dates. Once you do that, you should be able, with some work, to concatenate values to display your day of the week results in a single column, though.

If this can be done, though, I’d be fascinated to hear about it.

3

Ditto.

4

DOH! I thought so, but hey, you never know.

5

FWIW, it’s pretty trivial if you know you just want two dates. Add a column for the first date, a column for the second date and in your original column, write the formula


=TEXT(A2,"m/d/yyyy")&" & "&TEXT(B2,"m/d/yyyy")

then in your other column, the formula


=TEXT(A2,"dddd")&" & "&TEXT(B2,"dddd")

If you occasionally have need for just one date, add some ifs to your formulas. If you occasionally need three or more, it starts getting more complex.

6

Will you ever have more than two dates in the cell? I can sorta see a Rube Goldberg of SEARCH() and MID() functions to find the location of the “&”, split the string based on its postion, and then convert the date to the name of the day, but that’s going to quickly become unworkable if you might have 1,2,3…7, whatever &'s in there. It could be done a lot more cleanly if you want to write your own function in VBA, but at some point it will probably just be easier to enforce a standard format for the data. Where are you getting the data from?

7

Just because I like Rube Goldbergs…

OK, cell A1 has your dates, like “1/1/08 & 1/3/08 & 1/5/08”

In B1, you put the formula:
=MID(A1,1,SEARCH("&",A1,1)-1)
In C1, you put the formula:
=MID(A1,LEN(B1)+2,SEARCH("&",A1,LEN(B1)))
In D1, you put the formula:
=MID(A1,LEN(B1)+LEN(C1)+4,SEARCH("&",A1,LEN(B1)+LEN(C1) + 1))**

…and so on for a sufficient number of columns to cover the maximum number of dates you might have in A1. Note that the number after the LEN() statement and the number at the very end of the formula (in italics) increase by 2 and 1, respectively, every additional column. You can put these colums out of the way or hide them if you don’t want them to show up to users. This splits your dates into a varying number of multiple columns.

In E1 (or wherever you want the finished data to go, you put:
=TEXT(TRIM(B1),“dddd”)& " & " & TEXT(TRIM(C1),“dddd”)& " & " & TEXT(TRIM(C1),“dddd”)

You will probably want to put some “IF” logic in there so you won’t get “& &” with no dates between them.

Then, and this is the most important step, give yourself 50 lashes for having non-discrete data in spreadsheet cells :wink:

Best of luck!