Home > Validation > Drop Downs > Time Data Validation Hours and Minutes Create 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).

Introduction

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:

1. Production time must be equal to, or greater than, Defect time
2. Defect time must be less than, or equal to, Production time
3. Production time must be entered before Defect time can be entered (Defect drop downs do not work until Production time is entered

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.

Time Entry Sheet

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

Create the Hours Lists

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:

• Create an Hours List
• Add the Hours Formulas
• List of Valid Production Hours
• Sorted List of Production Hours
• Dynamic Named Range for Production Hours
• Drop Down List for Production Hours
• List of Defect Hours
• Dynamic Named Range for Defect Hours
• Drop Down List for Defect Hours

Create an Hours List

First, you will type and name a list of numbers. Follow these steps to create the basic list of hours:

1. In cell B1, type the heading, "Hours"
2. In cells B2:B11, enter the numbers 1-10 (You could type more numbers, if necessary. An arbitrary limit of 10 was set for this example)
   TIP: Type 1 and 2, select those cells, and drag the fill handle down to cell B11
3. To name the list of numbers, select cells B2:B11
4. Click in the Name box, to the left of the Formula Bar
5. Type a one-word name for the list -- Hours -- and press the Enter key to complete the name

Add the Hours Formulas

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:

1. Production hours must be equal to, or greater than, Defect hours. (Defect hours - 10)
2. Defect hours must be less than, or equal to, Production hours. (1 - Production hours)
3. Production hours must be entered before Defect hours can be entered (Defect drop down does not work until Production hours are entered)

For convenience, the hours from the Time Entry sheet are linked in cells D2 and D3

D5: Production Hours Minimum

In cell D5, the minimum number of hours allowed for production hours is calculated, with the following formula:

=IF(D3=0,MIN(Hours),D3)

• First, the Defect hours (D3) is checked. If that is zero, then the minimum number in the Hours range is used as the Production hours minimum.
• If the Defect hours (D3) is not zero, then the number of Defect hours is the miuimum, because Production hours must be equal to, or greater than, Defect hours
• In this example, Defect hours is 5, so that is the minimum for Production hours.

D6: Production Hours Maximum

In cell D6, the maximum number of hours allowed for production hours is calculated, with the following formula:

=MAX(Hours)

• Production hours can be any number equal to, or greater than, Defect hours, so the only limit is the highest number in the list of Hours.

D7: Production Hours Count

In cell D7, the total number of valid hours is calculated, with the following formula:

=D6-D5+1

• Subtract the minimum number (D5) from the maximum number (D6), then add 1
• This count will be used to create a dynamic list of valid numbers, for the Production Hours drop down list.

D10: Defect Hours Minimum

In cell D10, the minimum number of hours allowed for Defect hours is calculated, with the following formula:

=MIN(Hours)

• Defect hours can be any number lower than, or equal to, Production hours, so the only limit is the lowest number in the list of Hours.

D11: Defect Hours Maximum

In cell D11, the maximum number of hours allowed for Defect hours is calculated, with the following formula:

=D2

• Defect hours can be any number lower than, or equal to, Production hours, so the Productions Hours amount in cell D2 is used as the maximum..

D12: Defect Hours Count

In cell D12, the total number of valid number is calculated, with the following formula:

=D11-D10+1

• Subtract the minimum number (D10) from the maximum number (D11), then add 1
• This count will be used to create a dynamic list of valid numbers, for the Defect Hours drop down list

List of Valid Production Hours

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.

Sorted List of Production Hours

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

Dynamic Named Range for Production Hours

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.

1. Select cell H1, which contains the heading for the Production Hours column
2. On the Excel Ribbon, click the Formulas tab, and click Define Name
3. In the New Name window, type HoursProd in the Name box.
4. In the Refers To box, type the following OFFSET formula:
   =OFFSET(ListHours!$H$1,1,0,ListMins!$D$7,1)
   
5. Click OK to complete the name

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.

Production Hours Drop Down

The final step for production hours is to create the drop down list on the Time Entry sheet.

1. On the Time Entry sheet, select cell B4
2. On the Excel Ribbon, click the Data tab, and click Data Validation
3. In the Allow drop down, select List.
4. In the Source box, type: =HoursProd
5. Click OK to create the drop down list

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.

List of Defect Hours

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

Dynamic Named Range for Defect Hours

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.

1. Select cell J1, which contains the heading for the defect hours column
2. On the Excel Ribbon, click the Formulas tab, and click Define Name
3. In the New Name window, type HoursDef in the Name box.
4. In the Refers To box, type the following OFFSET formula:
   =OFFSET(ListHours!$J$1,1,0,ListHours!$D$7,1)
5. Click OK to complete the name

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

Defect Hours Drop Down

The final step for defect hours is to create the drop down list on the Time Entry sheet.

1. On the Time Entry sheet, select cell B8
2. On the Excel Ribbon, click the Data tab, and click Data Validation
3. In the Allow drop down, select List.
4. In the Source box, type: =HoursDef
5. Click OK to create the drop down list

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.

Create the Minutes Lists

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:

• Create a Minutes List
• Add the Minutes Formulas
• List of Valid Production Minutes
• Sorted List of Production Minutes
• Dynamic Named Range for Production Minutes
• Drop Down List for Production Minutes
• List of Defect Minutes
• Dynamic Named Range for Defect Minutes
• Drop Down List for Defect Minutes

Create a Minutes List

First, you will type and name a list of numbers. Follow these steps to create the basic list of minutes:

1. In cell B1, type the heading, "Minutes"
2. In cells B2:B61, enter the numbers 1-60 (Type 1 and 2, select those cells, and drag the fill handle down to cell B61)
3. To name the list of numbers, select cells B2:B11
4. Click in the Name box, to the left of the Formula Bar
5. Type a one-word name for the list -- Minutes -- and press the Enter key to complete the name

Create the Minutes Formulas

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:

1. If Production hours is greater than Defect hours, any number of minutes (1-60) can be entered for Production minutes
2. If Production hours is greater than Defect hours, any number of minutes (1-60) can be entered for Defect minutes
3. If Production hours is equal to Defect hours, Production minutes must be greater than or equal to Defect minutes (Defect minutes - 60)
4. If Production hours is equal to Defect hours, Defect minutes must be lower than or equal to Production minutes (1 - Production mnutes)

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.

D8: Production Minutes Minimum

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

• First, the Production hours (D2) is checked. If that is greater than the Defect hours (D3), any number of minutes (1-60) can be entered for Production minutes, so the minimum is set at 1.
• If Production hours is not greater, but Defect minutes (D6) is zero, any number of minutes (1-60) can be entered for Production minutes, so the minimum is set at 1.
• Otherwise, the Defect minutes in D6 is the minimum, because Production time must be equal to or greater than Defect time.
• In this example, Production Hours and Defect hours are equal, so the minimum for Production minutes is 32 -- the Defect minutes..

D9: Production Minutes Maximum

In cell D9, the maximum number of minutes (60) in an hour is typed into the cell. There is no formula.

• Production minutes can be any number equal to, or greater than, Defect minutes, so the only limit is the highest number in the list of minutes.

D10: Production Minutes Count

In cell D10, the total number of valid minutes is calculated, with the following formula:

=MIN(60,D9-D8+1)

• Subtract the minimum number (D8) from the maximum number (D9), then add 1
• The MIN function is used, with 60 as the first argument, to ensure that the result is not higher than 60
• This count will be used to create a dynamic list of valid numbers, for the Production Minutes drop down list.

D12: Defect Minutes Minimum

In cell D12, the minimum number of minutes (1) in an hour is typed into the cell. There is no formula.

• Defect hours can be any number lower than, or equal to, Production hours, so the only limit is the lowest number in the list of Minutes.

D13: Defect Minutes Maximum

In cell D13, the maximum number of hours allowed for Defect hours is calculated, with the following formula:

=IF(D2>D3,60,D5)

• First, the Production hours (D2) is checked. If that is greater than the Defect hours (D3), any number of minutes (1-60) can be entered for Production minutes, so the maximum is set at 60.
• If Production hours is not greater, the Production minutes in D5 is the maximum, because Defect time must be lower than or equal to Production time.
• In this example, Production Hours and Defect hours are equal, so the maximum for Defect minutes is 50 -- the Production minutes.

D14: Defect Minutes Count

In cell D14, the total number of valid number is calculated, with the following formula:

=MIN(60,D13-D12+1)

• Subtract the minimum number (D12) from the maximum number (D13), then add 1
• The MIN function is used, with 60 as the first argument, to ensure that the result is not higher than 60
• This count will be used to create a dynamic list of valid numbers, for the Defect Minutes drop down list.

List of Valid Production Minutes

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.

Sorted List of Production Minutes

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

Dynamic Named Range for Production Minutes

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.

1. Select cell H1, which contains the heading for the Production minutes column
2. On the Excel Ribbon, click the Formulas tab, and click Define Name
3. In the New Name window, type MinsProd in the Name box.
4. In the Refers To box, type the following OFFSET formula:
   =OFFSET(ListMins!$H$1,1,0,ListMins!$D$10,1)
5. Click OK to complete the name

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.

Production Minutes Drop Down

The final step for production hours is to create the drop down list on the Time Entry sheet.

1. On the Time Entry sheet, select cell C4
2. On the Excel Ribbon, click the Data tab, and click Data Validation
3. In the Allow drop down, select List.
4. In the Source box, type: =MinProd
5. Click OK to create the drop down list

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.

List of Defect Minutes

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

Dynamic Named Range for Defect Minutes

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.

1. Select cell J1, which contains the heading for the defect minutes column
2. On the Excel Ribbon, click the Formulas tab, and click Define Name
3. In the New Name window, type MinDef in the Name box.
4. In the Refers To box, type the following OFFSET formula:
   =OFFSET(ListMins!$J$1,1,0,ListMins!$D$14,1)
5. Click OK to complete the name

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

Defect Minutes Drop Down

The final step for defect minutes is to create the drop down list on the Time Entry sheet.

1. On the Time Entry sheet, select cell C8
2. On the Excel Ribbon, click the Data tab, and click Data Validation
3. In the Allow drop down, select List.
4. In the Source box, type: =MinDef
5. Click OK to create the drop down list

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.

Download the Sample File

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.