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

As luck would have it I am already doing drop down list for other information!!  So I guess add two columns for Beginning and Ending times.  Would I do the same for the date?  I know the formula for the day but this sheet will be used for the week.  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 help me with this?  I assume in the formula for time 5 is increments for 5 minutes, so if I wanted thi for every minute, I would replace the 5 with a 1?

Answer 4

They will have this time sheet for a week.  So everyday they will put the date:

Such as 

Monday 5/1/2014

Tuesday 5/2/2014

Wednesday 5/3/2014

so if I put the formual for today date I think that if On Wednesday they open the spreadsheet and Monday day will change to Wednesday date.  I want the correct date for each day they work.

It possible that they will have 3 entries for one day.  They may work he on 3 different job in a day.

Example

4

Monday 5/1/2014 8:00 a.m. to 10 a.m.

Monday 5/1/2014 10:30 a.m. to 12:00 pm

Monday 5/1/2014 2:00 p.m. to 5:00 p.m.

I have to run will check the link you sent when I get back!!!  

Thank you sooo much for your help!!!

Answer 5

Ok so the Time Formula is the solution I needed.  The calender thing is cool, but more than I need.  I need a formula for the date as the time.  I have to have the date for the whole year at least a month at a time but the whole year would be better.  

Thanks sooo much for the help you have given me already.  Going to play with the date.