SituationAllen's email from ExcelTips this past weekend included a question from a reader who wanted to know how to control worksheet inputs of dates with time. It seems some of his users were typing in junk, which meant date/time calculations could not be done.
A little reading between the lines indicated that in addition to forcing people to enter date/time in the right format there might also be need to add date/time stamps, alleviating the need to type in the date and time and completely avoiding the controversy. This led me to five different solutions, each with its own purpose and situational applicability. One of the solutions uses settings that can be made from the Ribbon's Data Menu, the other four depend on VBA and leverage the Worksheet's Event-triggered Subs.
- Data Validation can be used to force the entry of a date/time. One of the additional requirements, from Excel, is that the date/time must meet a criteria. There is no method that allows any and all date-times. This is an opportunity to support business rules, like a planning date/time must be later than the current date-time.
- Data Type validation using VBA. This would use the Worksheet_Change event, then make certain the entry was a date-time. What is included here goes two steps further, validating for the date range as possible with Data Validation the confirming that the time would be during a specific part of the day.
- Date-Time Stamp on Entry. This also uses the Worksheet_Change event, but instead of validating what was entered, once the new information is added the code puts the current date-time in a cell of the same row.
- Date-Time Stamp on Double-Click. As expected, this uses the Worksheet_DoubleClick Event to determine when to add the date-time stamp.
- Date-Time Stamp on completion. This is another use of the Worksheet_Change event, this time when all pertinent fields of the row are entered then a date-time stamp is added.
Data Validation (Ribbon)
Why use this method
- There is no VBA code, meaning fewer security concerns
- Inputs can be controlled not only for data type but also for date ranges, e.g. a the date/time cannot be more than 30 days from now and must be after now.
- A date-range must be specified, meaning one cannot simply force only data type for validation and the rules for the valid date/time range must be stated in a way that is flexible and follows the business needs.
- Date-ranges are simple before/after/between. Weekends are as valid as workdays and ranges for times of day cannot be specified, i.e. 11:00 PM would pass even if it is not during business hours.
How to use this method
- Select the range where the validation is needed. In this case, the named range "When".
- Access the Data menu, then click the Data Validation entry
- Enter the validation rules. In this case, since this is a forward-looking (planning) date, rather than one that is backward-looking (historical, reporting) we've set the acceptable range between now and 30 days from now.
|Figure 1 - Date Validation - Next 30 Days|
- If desired, click on the Input Message and add a message to be displayed whenever a cell in the range is selected.
NOTE: If the Allow drop-down in the Settings tab is set to "Any value" this message will still be displayed.
|Figure 2 - Data Validation - Input Message|
- If desired, click on the Error Alert and add a message to be displayed if the entry fails data validation.
|Figure 3 -Data Validation - Error Alert|
Set-up for VBA Solutions
|Figure 4 - Initial Worksheet|
- Start Entry. When the first entry of the row is completed, this will be populated with the date-time stamp.
- Uncontrolled Data entry columns. Free-form entry for the sake of this example. Could use data validation for either/both as well as additional columns. Two used to show the difference between the event when the first one is populated and when all of the user inputs of the row are complete.
- Details Completed. Documents with a date-time stamp when the Who and Need data entry columns for this row are both complete.
- Action to Take. Free form entry describing what action will be taken,
- Planned Action Date. Date/Time when the person making the entry expects to perform Action to Take. Entered by the user typing the date and time.
- Plan Confirmed. Field populated by a double click. Meaning could be when all entries are complete or when the action has been completed.
|Figure 5 - Data Menu|
- Access the Formulas menu of the ribbon
- Select the table, i.e. A:F in this case.
- Click on Create from Selection.
- Click the OK Button
|Figure 6 - Adding Range Names|
|Figure 7 - Name Manager with Named Ranges|
Controlling where Users May Type/Paste
- Select the ranges for What, When, and Who. (This can be done with all three at once or each individually.)
- Right-click, causing the context menu to be displayed.
- Click on Format Cells.
- When the Format Cells dialogue is displayed, click of the Protection tab if it is not the active tab.
- Change the "Locked" from checked to unchecked.
- Click OK.
|Figure 8 - Cells Unlocked|
- Right-click on the worksheet's tab
- Select Protect from the context menu
|Figure 9 - Worksheet Tab Context Menu|
|Figure 10 - Protect Sheet Dialogue - Default|
- Optional: Scroll Down, and select Use Autofilter
|Figure 11 - Worksheet Protect - Features Added|
- Enter a password, if desired.
- Click OK
Data Validation (VBA)
If Not Intersect(Target, Range("Planned_Action_Date")) Is Nothing Then
If Not IsValidPlanDate(Intersect(Target, Range("Planned_Action_Date"))) Then
Target.Value = InputBox("The time needs to be between " _
& Format(DateAdd("h", EarliestStartHour, 0), "hh:mm") _
& " and " & Format(DateAdd("h", LatestStartHour, 0), "hh:mm") _
& ". Please re-enter", "When", Target.Formula)