Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
calendar control for week day
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
calendar control for week day
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
calendar control for week day
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 "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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calendar control | Excel Discussion (Misc queries) | |||
Calendar Control | Excel Worksheet Functions | |||
Tie a Calendar week to a Scoped Projected Week | Excel Worksheet Functions | |||
Calendar Template with week numbers | Excel Discussion (Misc queries) | |||
How do I make a one-week calendar? | Excel Discussion (Misc queries) |