Cells with time constraint
 

Dear experts,

In my worksheet, column A is for default dates of each month and column B is
for recording staff's reporting time. I want the cells in column B can only
accept the current computer time between 8:30 am to 8:45 am on current date
only. For example, the current date and time is 8 March 2010 and 8:37 am. In
cell A3, the date is already 8 March 2010, then the user inputs the time 8:37
am in B3. Either the user inputs the time next to the current date is
incorrect or input the time outside the default time range - 8:30 am to 8:45
am, the cell will not accept the input. Is it possible? If yes, what will be
the formula or macro code? Please advise.

Thanks in advance.

Cells with time constraint
 

Use Data Validation, with a type of Custom, and a formula of

=AND(INT(B3)=A3,MOD(B3,1)=--"08:30:00",MOD(B3,1)<=--"08:45:00")

--

HTH

Bob

"Freshman" wrote in message
...
Dear experts,

In my worksheet, column A is for default dates of each month and column B
is
for recording staff's reporting time. I want the cells in column B can
only
accept the current computer time between 8:30 am to 8:45 am on current
date
only. For example, the current date and time is 8 March 2010 and 8:37 am.
In
cell A3, the date is already 8 March 2010, then the user inputs the time
8:37
am in B3. Either the user inputs the time next to the current date is
incorrect or input the time outside the default time range - 8:30 am to
8:45
am, the cell will not accept the input. Is it possible? If yes, what will
be
the formula or macro code? Please advise.

Thanks in advance.

Cells with time constraint
 

Thanks, Bob. I'll try tomorrow morning. Best regards.

"Bob Phillips" wrote:

Use Data Validation, with a type of Custom, and a formula of

=AND(INT(B3)=A3,MOD(B3,1)=--"08:30:00",MOD(B3,1)<=--"08:45:00")

--

HTH

Bob

"Freshman" wrote in message
...
Dear experts,

In my worksheet, column A is for default dates of each month and column B
is
for recording staff's reporting time. I want the cells in column B can
only
accept the current computer time between 8:30 am to 8:45 am on current
date
only. For example, the current date and time is 8 March 2010 and 8:37 am.
In
cell A3, the date is already 8 March 2010, then the user inputs the time
8:37
am in B3. Either the user inputs the time next to the current date is
incorrect or input the time outside the default time range - 8:30 am to
8:45
am, the cell will not accept the input. Is it possible? If yes, what will
be
the formula or macro code? Please advise.

Thanks in advance.




.

Cells with time constraint
 

Hi Bob,

I tried this morning but it didn't work. Today is 10 Mar 2010. In my
worksheet, A11 is today's date, i.e. 10 Mar 10. So, in cell B11, I input the
current time 08:40 but an error message prompt out stating that the entry is
invalid because the cell is restricted. Please note that I have formatted the
cells in column B to time format : 00:00:00 already. Please advise what's
wrong I have done and how to correct it.

Thanks again.

"Bob Phillips" wrote:

Use Data Validation, with a type of Custom, and a formula of

=AND(INT(B3)=A3,MOD(B3,1)=--"08:30:00",MOD(B3,1)<=--"08:45:00")

--

HTH

Bob

"Freshman" wrote in message
...
Dear experts,

In my worksheet, column A is for default dates of each month and column B
is
for recording staff's reporting time. I want the cells in column B can
only
accept the current computer time between 8:30 am to 8:45 am on current
date
only. For example, the current date and time is 8 March 2010 and 8:37 am.
In
cell A3, the date is already 8 March 2010, then the user inputs the time
8:37
am in B3. Either the user inputs the time next to the current date is
incorrect or input the time outside the default time range - 8:30 am to
8:45
am, the cell will not accept the input. Is it possible? If yes, what will
be
the formula or macro code? Please advise.

Thanks in advance.




.

Cells with time constraint
 

That formula expects you to use date and time, otherwise what is the point
of the date in A?

--

HTH

Bob

"Freshman" wrote in message
...
Hi Bob,

I tried this morning but it didn't work. Today is 10 Mar 2010. In my
worksheet, A11 is today's date, i.e. 10 Mar 10. So, in cell B11, I input
the
current time 08:40 but an error message prompt out stating that the entry
is
invalid because the cell is restricted. Please note that I have formatted
the
cells in column B to time format : 00:00:00 already. Please advise what's
wrong I have done and how to correct it.

Thanks again.

"Bob Phillips" wrote:

Use Data Validation, with a type of Custom, and a formula of

=AND(INT(B3)=A3,MOD(B3,1)=--"08:30:00",MOD(B3,1)<=--"08:45:00")

--

HTH

Bob

"Freshman" wrote in message
...
Dear experts,

In my worksheet, column A is for default dates of each month and column
B
is
for recording staff's reporting time. I want the cells in column B can
only
accept the current computer time between 8:30 am to 8:45 am on current
date
only. For example, the current date and time is 8 March 2010 and 8:37
am.
In
cell A3, the date is already 8 March 2010, then the user inputs the
time
8:37
am in B3. Either the user inputs the time next to the current date is
incorrect or input the time outside the default time range - 8:30 am to
8:45
am, the cell will not accept the input. Is it possible? If yes, what
will
be
the formula or macro code? Please advise.

Thanks in advance.




.