Drop Down Hours and Minutes

Last week, someone asked me how to create drop down lists for hours and minutes, and also control the total time that was entered. Production time and Defect time would be entered, and Defect total time could not be greater than the Production total time.

He sent a sample file, with drop down lists in place, but they allowed invalid times to be entered. Was there any way to make it work?

timeentry01

It Can’t Be Done

My first response was, “You can’t do that with drop down lists. Just type the hours and minutes, and use a data validation formula to check the totals.” I’ve used that technique in other workbooks, to compare totals, like the budget example, shown below.

datavaltotallimit

The reply to my response was polite, but persistent, “Thank you, and if you can think of a way to use drop downs, please let me know.”

Accepting the Challenge

That made me start thinking about ways to control the time entry drop downs. A cell can’t combine drop down lists with custom data validation rules, but maybe I could create lists with only the valid numbers.

So, I tackled the challenge, and found a way to create dynamic named ranges with the valid numbers for each list – Production Hours, Defect Hours, Production Minutes and Defect Minutes. I used the OFFSET function for the Refers to formulas, but you could use the INDEX function if you prefer.

timeentry13

Limit the Drop Down Lists

Each drop down list is based on one of the dynamic named ranges, and they follow these rules:

  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

In the screen shot below, you can see the Defect Hours drop down. Production Hours has been set at 4 hours, so the Defect Hours drop down only shows the numbers from 1 to 4.

timeentry15

There are controls on the Production drop downs too, to prevent people from changing to an invalid time, after entering Defect times.

In the screen shot below, the Production Minutes has a minimum of 32, so it can’t be set lower than the Defect time.

timeentry20

Calculate the Minimums and Maximums

There are worksheet formulas that calculate the minimum and maximum number for each list, and the number of items. You can see the formulas, and their explanations, in the sample file, and the written instructions.

timeentry06

Download the Sample File

To see how the drop down lists work, download the sample file from the Excel Data Validation – Select Hours and Minutes page on my Contextures website.

_____________