date and time picker controls

[Janis]

Is there a date or time Picker control in Excel 2004? Or do you have to use
the list wizard. I'm following the Georgetown Cleaning Services tutorial.
Otherwise it looks like I ahve to create a comboBox but how do I attach a
date calendar so the user can pick any date.

thanks

 

[jr]

Is there a date or time Picker control in Excel 2004? Or do you have to use
the list wizard. I'm following the Georgetown Cleaning Services tutorial.
Otherwise it looks like I ahve to create a comboBox but how do I attach a
date calendar so the user can pick any date.

thanks

Sorry I did a search and found out there is no date picker but you can
use a drop down (which I assume is a combo box?)
and validation. I have the combo box on my spreadsheet but can't
figure out where the validation goes. How do you put in all the dates
possible?

 

[JE McGimpsey]

Sorry I did a search and found out there is no date picker but you can
use a drop down (which I assume is a combo box?)
and validation. I have the combo box on my spreadsheet but can't
figure out where the validation goes. How do you put in all the dates
possible?

I've done this before using a list on a hidden sheet. For example:

Say the user chooses the year and month number in cells A1 & B1 of
Sheet1, and the day goes in C1.

On my hidden sheet, I enter 1-28 in A1:A28, then

A29: =IF(MONTH(DATE(Sheet1!$A$1,Sheet1!$B$1,29))=Sheet1!$B$1,29,"")
A30: =IF(MONTH(DATE(Sheet1!$A$1,Sheet1!$B$1,30))=Sheet1!$B$1,30,"")
A31: =IF(MONTH(DATE(Sheet1!$A$1,Sheet1!$B$1,31))=Sheet1!$B$1,31,"")

I then use Insert/Name/Define to define a dynamic range:

Name in Workbook: DayList
Refers to: =OFFSET(Hidden!$A$1,0,0,COUNTIF(Hidden!$A$1:$A$31,">0"),1)

Finally, in Sheet1!C1, I choose Data/Validation:

Allow: List
Source: =DayList

Now C1's validation will contain all the valid dates for that month.