retired bill,

Do you need the day of the week to be in a separate cell? You could just
format cell B7 with a date format that includes the day of the week in the
same cell as the date.

For example, a cell format of "dddd, mmmm dd, yyyy" will display your date
in B7 like this:

Wednesday, May 06, 2009

Otherwise, I think I would just use the WEEKDAY() function in combination
with a VLOOKUP() function and build a lookup table with the 7 days of the
week with their corresponding numbers.

If you enter...

=WEEKDAY(B7)

....in cell C7, then it will return 1-7 for the day of the week.

To use that with VLOOKUP(), first, in an out-of-the-way area of your sheet,
create the lookup table that will consist of the digits 1-7 in the first
column and the names of the days from Sunday to Saturday in the second
column. Maybe in cells Y1:Z8 (using the first row as column headers...if
you want them...if not, use Y1:Z7). Then your formula in C7 would look like
this:

If you used column headers:
=VLOOKUP(WEEKDAY(B7),$Y$2:$Z$8,2,0)

Or, if you didn't use column headers:
=VLOOKUP(WEEKDAY(B7),$Y$1:$Z$7,2,0)

HTH,

Conan Kelly





"retired bill" wrote in message
...
I figured out how to add a calendar control to my excel spreadsheet.
For example, when I click in cell B7, the calendar appears, I click on a
November 11, the date of 11/11/08 gets entered into cell B7, and the
calendar
disappears. This works Fine. However, what I would like to be able to
set
up
is when the person clicks on November 11, the date of November gets
entered
into cell B7 AND the corresponding weekday (in this case, Tuesday) gets
automatically entered into the cell next to it, C7.
i.e. cell B7 returns the DATE
cell C7 retunrs the DAY of week
The calendar control is in column B - Range B7:B