If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. . |
#4
|
|||
|
|||
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. . |
#5
|
|||
|
|||
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. . |
Thread Tools | |
Display Modes | |
|
|