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?

drop down lists for time entry

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.

timeentry23a

_____________

You may also like...

4 Responses

  1. Jeff Weir says:

    Some nasty user can still make the defect time up to 60 minutes greater than the production time, in the case where they select minutes first and hours second in the Defect dropdowns. I’m sure there’s probably a formula workaround, though.

    • Thanks Jeff! You’re right, and a simple fix would be to make the hour drop downs dependent on what’s in the minutes cell. If minutes have been entered, the hours drop down could show a list that just has a zero in it.

  2. Sherrie Gowans says:

    Debra, Thanks, thanks, many thanks!! I’ve been puzzling for 2 months on how to keep track of my own (shift) work/vacation/meeting hours in Excel. (Even lay at night trying to figure out the formula’s). I am a newbie to Excel but managed to create drop-down lists in cells to enter my (shift)work codes ,then LOOKUP for daily then Total. But I could not figure out (had even asked two Excel “gurus” I know for advice on) how to total “vacation” separately. They couldn’t help. I tried every possible Function I could think of, but failed. Then I found your Match/Index Function tutorial this afternoon while searching the web. FANTASTIC!. Now I find this tutorial, even better! Your information is logical and decipherable to folk like me who use Excel as a hobby, rather than for work. (Now I just need to translate the Functions correctly to Dutch and I’m on my way. Keep up the great work with your tutorials :o)

    • @Sherrie You’re welcome! Thanks for letting me know that it helped you with your vacation calculations.
      And good luck with the translations! If you open the workbook in a Dutch version of Excel, they should convert to that language automatically, I think.

Leave a Reply to Jeff Weir Cancel reply

Your email address will not be published. Required fields are marked *