Weekly work time schedule with scroll

[Adas]

Hello to everyone,

I'm a rookie when it comes to Excel(2002) and I want thank you for any type of advice you can give me.

I'm trying to create a schedule for my department managers to use to put in hours for their employees in their departments. I'm trying to make it as easy for them as possible. I will start with the first issue I have and give you a run down of what else I 'm trying to accomplish.

My first question is how can I create a drop down menu for a cell for time selection with 15 min. intervalls with AM/PM?

The next steps I want try to create are:
-have a total # of hours for all 7 days per employee
-if the hours for a shift total 8.5 hours to subtract 30 min for lunch in my total hours count for that day
- to have the spreadsheet locked so that nobody can make any changes other than in cells that I have color coded for them to change times so none of the formulas get messed up.

Let's start with the drop down menu. I know I can create a column with a list of times, but is there an easier way that to type all of the times?

 

[texasalynn]

Welcome to MrExcel board...

for creating a drop-down with time in 15 minute increments, create another sheet with the time

Excel Workbook
	A
2	12:00 AM
3	12:15 AM
4	12:30 AM
5	12:45 AM
6	1:00 AM
7	1:15 AM
8	1:30 AM
Sheet1

Excel 2003

Cell Formulas
Range	Formula
A3	=A2+0.0104166666666667
A4	=A3+0.0104166666666667
A5	=A4+0.0104166666666667
A6	=A5+0.0104166666666667
A7	=A6+0.0104166666666667
A8	=A7+0.0104166666666667

copy the formula down for the entire 24 hours. Then select those cells and name the range "DTime". Then you can go to your sheet and create the drop down by selecting Data - Validation -Allow: List - Source: =DTime

 

[Adas]

Thank you, I got that out of the way

My next step is to calculate total hours for each employee.
The way I have it set up is in column G is starting time and column I is the ending time for one day. how do I get a total count of hours for that day? Between column G and I, I have column H as a seperation with a "-". I"m also trying to have a formula so that if the total hours for the day = to 8.5 hours i can suptract 30 min for lunch before I total all the hours for the week per person.

 

[texasalynn]

time can be added almost like regular numbers (some anomialies)

=I2 + G2

 

[Adas]

Subtracting from another cell

I'm kinda stuck.

I'm trying to make a time work schedule and what I'm trying to do is after I calculate total amount of hours in a day in a seperate chart. somehow I want to be able to subtract .5 hours of off 8.5 hours shift which includes .5 hours for lunch that I do not want to include in my total work hours for the week.

Any ideas???

 

[texasalynn]

what have you tried? post samples of your data