calendar control for week day

[retired bill]

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

 

[Conan Kelly]

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

 

[John]

Hi
If you must have the day in a separate cell, just custom format the cell to
"dddd" and reference =B7 that will give you the day.
HTH
John