How can I set date range? (data entered could be 01.07.2000 6:35 AM, or 12.10.1978 11:45 PM, or 13.09.2012 11:17 AM....)
I just want to know what the minimum and maximum date values that you want to allow them to enter is.
For the sake of this example, let's just say it is 1/1/1990 to 1/1/2030.
Assuming that you want to apply this on the range E2:E100, do the following.
1. Select the range E2:E100
2. Right-click on any of those selected cells and "Format Cells"
3. On the "Number" tab, select "Custom"
4. Enter this is the "type" box:
dd.mm.yyyy h:mm AM/PM and click "OK"
5. Make sure the range E2:E100 is still selected
6. Go to the "Data" menu, and on the "Data Tools" ribbon, select "Data Validation"
7. On the "Settings" tab, set these values:
- Allow: Date
- Data: between
- Start date: 1/1/1990
- End date: 1/1/2030
8. Go to the "Input Message" tab, and enter any instructions/message you would like them to see
9. Go to the "Error Alert" tab, and set these values:
-
Style:
Stop
-
Title: whatever you would like to be
-
Error message: whatever you would like it to say when they enter bad data
10. Click "OK"
Now, try it out. It should do what you want. It will force them to enter a valid date/time between those date ranges.
If this suffices, then we do not really need VBA. You would just invoke VBA if you were worried about them removing Data Validation or changing the format of the cells (but then again if they were intent on doing that, they could just disable VBA code too, and that wouldn't run).