I want the timesheet to only allow times in increments of 15 min. I have a
list of 96 times from 0:00 to 23:45 named ValidTimes. I set up validation on
a cell with this named list and to allow in-cell dropdown.

Selecting any value from the dropdown list validates fine.

I also want to allow times to be typed into the cell. Some values validate
OK and others do not. Below is an example:
Time Validates OK
0:15 y
0:30 y
0:45 y
1:00 n
1:15 n
1:30 y
1:45 n
2:00 n
2:15 y
2:30 n

How can I fix it so that typed in values that are in the list validate
correctly?
--
Thanks for your help.

You can do this with the Data Validation tool on the Data menu. Select
the range of cells whose input you want to validate, open the
Validation dialog, and choose Custom in the Allow list. There, enter
the formula

=MOD(MINUTE(F1),15)=0

Change the cell reference F1 to the first cell of the list of cells to
be validated.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Fri, 31 Oct 2008 15:50:01 -0700, Extraordinaire
wrote:

I want the timesheet to only allow times in increments of 15 min. I have a
list of 96 times from 0:00 to 23:45 named ValidTimes. I set up validation on
a cell with this named list and to allow in-cell dropdown.

Selecting any value from the dropdown list validates fine.

I also want to allow times to be typed into the cell. Some values validate
OK and others do not. Below is an example:
Time Validates OK
0:15 y
0:30 y
0:45 y
1:00 n
1:15 n
1:30 y
1:45 n
2:00 n
2:15 y
2:30 n

How can I fix it so that typed in values that are in the list validate
correctly?

Thanks Chip. Chip's solution works as he says. Unfortunately, I would like
to validate both a typed in entry and a selected entry from a drop-down list.
Chip's solution does not provide a dropdown list.

A validation list usually provides both types of functionality but I can't
seem to get it to work with Time values.

Regards.

"Chip Pearson" wrote:

You can do this with the Data Validation tool on the Data menu. Select
the range of cells whose input you want to validate, open the
Validation dialog, and choose Custom in the Allow list. There, enter
the formula

=MOD(MINUTE(F1),15)=0

Change the cell reference F1 to the first cell of the list of cells to
be validated.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Fri, 31 Oct 2008 15:50:01 -0700, Extraordinaire
wrote:

I want the timesheet to only allow times in increments of 15 min. I have a
list of 96 times from 0:00 to 23:45 named ValidTimes. I set up validation on
a cell with this named list and to allow in-cell dropdown.

Selecting any value from the dropdown list validates fine.

I also want to allow times to be typed into the cell. Some values validate
OK and others do not. Below is an example:
Time Validates OK
0:15 y
0:30 y
0:45 y
1:00 n
1:15 n
1:30 y
1:45 n
2:00 n
2:15 y
2:30 n

How can I fix it so that typed in values that are in the list validate
correctly?