How to create a drop down Time list

Question

Is it possible to create a drop down box with Time? A drop box with time to choose from :

I have attached a file. This going to be a time sheet and instead of employees putting Start Time and Ending time.  But I want them to choose the time from a drop box.  While I at it should get a formula for the date.  I have the formula for the day but I don't want each day to change to current date.

Date	Employee Name	Start Time	End Time
5/1/2014	Mark Brown	8:00 AM	5:00 PM
5/2/2014	Mark Brown	9:00 AM	1:15 PM
5/2/2014	Mark Brown	7:30 AM	5:00 PM

Thanks for help in Advance

Answer 1

You can use Data Validation with a list.  Follow the steps below.

On one of your worksheets, create a list of allowable time entries.  The example creates a list that has time entries spaced 5 minutes apart:

Select the entire list (not including the header!), and give it a name by clicking in the text box to the left of the formula box and entering a name:to the left of the formula box and entering a name:

Then select the cell(s) that you want the drop-down to appear in, and go to the Data tab on the Ribbon.  Click on Data Validation, select Data Validation... and then pick "List" from the drop-down.  In the Source box, enter "=MyTimeList" (without the quote marks).

Now you should have drop-downs for selecting time!

Hope this helps,

Eric

Answer 2

Yes, the 5 in my example is for 5 minute increments.

I'm not sure I understand your statement "So I don't want the date to change but I want to be able to choose a new date for each day?"  Can you provide an example of how you want dates to work?

You can use Data Validation to ensure that proper dates are entered into cells, but I would not recommend using a drop-down list, unless you know exactly the date range you want in the list.

A better approach to entering dates in a list is to use a date control like the Microsoft  Date and Time Picker.  See, for example, the link below.

http://danielcurran.com/instructions/insert-a-drop-down-calendar-menu-in-excel-choose-a-date/

Answer 3

I figure out the date solution.  I entered 1/1/2014 in A2 - choose Fill - Series- Columns - date- day - step value 1 and highlighted the row A2 and went down 365 in the same column to A366 - Ctrl D - it filled in the dates for the entire year.  It even number the correct day for each month.  Febuary had 28 days!!! And since I already using Data Valadation - drop down box, works great!!!

Answer 4

I'm trying to do the same but in the drop down list shows decimals not actual times that is what I was looking to get