Cells with time constraint

[Freshman]

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.

 

[Bob Phillips]

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")

 

[Freshman]

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

 

[Freshman]

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]

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