Home > Validation > Drop Downs > Time Data Validation Hours and MinutesCreate drop down lists for hours and minutes. In this example, data validation rules ensure that total time 2 (defect time) cannot be greater than Total time 1 (production time). |
In this sample file, the goal is to have drop down lists to select the hours and minutes for time data entry. Production time and Defect time will be selected from drop down lists, and the total Defect time cannot be greater than the total Production time.
Basic rules for the time entry are:
To allow people to select from a list, and also control the total minutes, this example uses dynamic named ranges. A valid list of hours and minutes is created with formulas, and only the valid numbers are shown in the drop down lists.
The instructions below show how to set up the workbook with dynamic ranges, and create drop down lists based on those ranges.
Note: Data validation is not foolproof. It can be circumvented
by pasting data into the cell, or by choosing Clear > Clear All, on
the Ribbon's Home tab.
The first sheet in the workbook is named Time Entry, and the screen shot below shows the simple layout. Drop down lists will be added later, in cells B4, C4, B8 and C8.
In cell D4, a formula calculates the total number of minutes for Production Time. The formula multiplies the number of hours in B4 by 60, and adds the number of minutes in cell C4:
=(B4*60)+C4
To calculate Total Defect time, the formula is copied to cell D8, where it becomes:
=(B8*60)+C8
The second sheet in the workbook is named ListHours. It contains a typed list of hours, and formulas for the drop down lists of hours on the Time Entry sheet.
There are several steps for setting up the hours sheet, described below:
First, you will type and name a list of numbers. Follow these steps to create the basic list of hours:
Next, on the ListHours sheet, you'll add formulas that will be used to create the dynamic lists of valid hours. The formulas are entered in column D, as shown in the screen shot below. The formulas are explained below the screen shot.
NOTE: An arbitrary number of hours (1-10) has been set for this example.
The basic rules for the hours are:
For convenience, the hours from the Time Entry sheet are linked in cells D2 and D3
In cell D5, the minimum number of hours allowed for production hours is calculated, with the following formula:
=IF(D3=0,MIN(Hours),D3)
In cell D6, the maximum number of hours allowed for production hours is calculated, with the following formula:
=MAX(Hours)
In cell D7, the total number of valid hours is calculated, with the following formula:
=D6-D5+1
In cell D10, the minimum number of hours allowed for Defect hours is calculated, with the following formula:
=MIN(Hours)
In cell D11, the maximum number of hours allowed for Defect hours is calculated, with the following formula:
=D2
In cell D12, the total number of valid number is calculated, with the following formula:
=D11-D10+1
The production hours cannot be lower than the defect hours, so a formula will create a list of valid hours -- numbers that are equal to, or higher than, the Defect Hours. For testing, on the Time Entry sheet, Production Hours is set at 4, and Defect Hours is set at 3.
In cell G2, the following formula compares the number in B2 to the production hours minimum in cell D5. If B2 is less than D5, an empty string ("") is returned. Otherwise, the number in B2 is returned.
=IF(B2<$D$5,"",B2)
Then, the formula is copied down to row 11. The list of valid numbers, returned by the formula, start at 3, and go to 10.
In the drop down list on the Time Entry sheet, the numbers should appear, but not the blank cells. To create the list of numbers, the SMALL function is used in cell H2. The formula compares the number in B2 to the production hours count in cell D7. If B2 is less than D7, an empty string ("") is returned. Otherwise, the nth smallest number is returned, where "n" is the number in column B, in the current row.
=IF(B2>$D$7,"",SMALL($G$2:$G$11,B2))
Next, create a dynamic named range for the list of production hours in column H. The OFFSET formula creates a range that includes all the numbers, but not the blank cells.
The OFFSET function has 5 arguments: =OFFSET(reference,rows,cols,height,width)
This OFFSET formula creates a named range that offsets from cell H1, goes down 1 row, and 0 columns to the right. The number of rows is based on cell D7 (Production Hours Count), and there is 1 column.
The final step for production hours is to create the drop down list on the Time Entry sheet.
To test the drop down, select cell B4, and click the arrow. Only the valid hours appear in the drop down list, with no blanks shown. Production hours can't be lower than Defect Hours (3), so 3 is the lowest valid number.
Next, create a list of valid Defect hours. This list will start at 1, will end at the number of Production Hours (cell D2)
Enter this formula in cell J2, and copy it down to row 11:
=IF($D$2=0,"",IF(B2<=$D$2,B2,""))
If the Production Hours cell is empty (zero), the result will be an empty string ("").
If the number in column B, in the current row, is less than or equal to the number in cell D2, the result is the number in column B. Otherwise, the result will be an empty string ("").
Next, create a dynamic named range for the list of defect hours in column J. The range will include all the numbers, but not the blank cells.
This formula will create a named range that starts one row below cell H1, and the number of rows is based on cell D7 (Defect Hours Count).
The final step for defect hours is to create the drop down list on the Time Entry sheet.
To test the drop down, select cell B8, and click the arrow. Only the valid hours appear in the drop down list, with no blanks shown. Defect hours can't be higher than Production Hours (4), so 4 is the highest valid number.
The third sheet in the workbook is named ListMins. It contains a typed list of minutes, and formulas for the drop down lists of minutes on the Time Entry sheet.
There are several steps for setting up the minutes sheet, described below:
First, you will type and name a list of numbers. Follow these steps to create the basic list of minutes:
Next, on the ListMins sheet, you'll add formulas that will be used to create the dynamic lists of valid minutes. The formulas are entered in column D, as shown in the screen shot below. The formulas are explained below the screen shot.
The basic rules for the minutes are:
For convenience, the hours and minutes from the Time Entry sheet are linked in cells D2, D3, D5, and D6. For testing, on the Time Entry sheet, Production is set at 4 Hours and 50 Minutes, and Defect is set at 4 Hours and 32 Minutes.
In cell D8, the minimum number of minutes allowed for production minutes is calculated, with the following formula:
=IF(D2>D3,1,IF(D6=0,1,D6))
In cell D9, the maximum number of minutes (60) in an hour is typed into the cell. There is no formula.
In cell D10, the total number of valid minutes is calculated, with the following formula:
=MIN(60,D9-D8+1)
In cell D12, the minimum number of minutes (1) in an hour is typed into the cell. There is no formula.
In cell D13, the maximum number of hours allowed for Defect hours is calculated, with the following formula:
=IF(D2>D3,60,D5)
In cell D14, the total number of valid number is calculated, with the following formula:
=MIN(60,D13-D12+1)
The production total cannot be lower than the defect total, so a formula will create a list of valid minutes, based on the formulas in column D.
In cell G2, the following formula compares the number in B2 to the production minutes minimum in cell D8. If B2 is less than D8, an empty string ("") is returned. Otherwise, the number in B2 is returned.
=IF(B2<$D$8,"",B2)
Then, the formula is copied down to row 61. In this example, the list of valid numbers, returned by the formula, start at 32, and go to 60.
In the drop down list on the Time Entry sheet, the numbers should appear, but not the blank cells. To create the list of numbers, the SMALL function is used in cell H2. The formula compares the number in B2 to the production minutes count in cell D10. If B2 is less than D10, an empty string ("") is returned. Otherwise, the nth smallest number is returned, where "n" is the number in column B, in the current row.
=IF(B2>$D$10,"",SMALL($G$2:$G$61,B2))
Next, create a dynamic named range for the list of production minutes in column H. An OFFSET formula creates a range that includes all the numbers, but not the blank cells.
This OFFSET formula creates a named range that offsets from cell H1, goes down 1 row, and 0 columns to the right. The number of rows is based on cell D10 (Production Minutes Count), and there is 1 column.
The final step for production hours is to create the drop down list on the Time Entry sheet.
To test the drop down, select cell C4, and click the arrow. Only the valid minutes appear in the drop down list, with no blanks shown. Production total can't be lower than Defect total, so 32 is the lowest number shown.
Next, create a list of valid Defect minutes. This list will start at 1, will end at the number calculated in cell D13 -- Defect Minutes Max.
Enter this formula in cell J2, and copy it down to row 61:
=IF(B2<=$D$13,B2,"")
If the number in column B, in the current row, is less than or equal to the number in cell D13 (Defect Minutes Max), the result is the number in column B. Otherwise, the result will be an empty string ("").
Next, create a dynamic named range for the list of defect minutes in column J. The range will include all the numbers, but not the blank cells.
This formula will create a named range that starts one row below cell
H1, and the number of rows is based on cell D14 (Defect Minutes Count).
The final step for defect minutes is to create the drop down list on the Time Entry sheet.
To test the drop down, select cell C8, and click the arrow. Only the valid minutes appear in the drop down list, with no blanks shown. Defect total can't be higher than Production total, so 50 is the highest number of minutes allowed.
You can download the sample time entry file here: timeentrylimit.zip
The zipped file is in xlsx format, and does not contain macros. Enter production time, then select hour and minutes for defect time. Must not be greater than production time, and drop down lists control the valid times.
Last updated: January 22, 2023 3:55 PM