I'm currently handwriting my schedules at work so i'm trying to create a spreadsheet to do this for me. i'd consider myself an intermediate in the world of excel; i've created a master spreadsheet with seven daily sheets each containing drop down lists to choose schedule in/out and break times for fifteen employees. however, i am struggling with the ability to program excel to calculate the number of hours scheduled. what kind of formula do i need to use to accomplish this? some info.... column a is "employee name" column b is "start time" column c is "finish time" column d is "break time", with the value of half an hour then, columns e thru ab represent every hour of the day. how do i get my entered data in columns a thru c to populate in their respective positions in columns e thru ab. i'm pretty sure im on the right track when i think that by entering for example eight am to four pm with a half hour break at noon, i need each cell from eight am to four pm to reflect the whole number one, with the exception of the noon hour which will reflect a .5 then way over at cell ac which will be hidden, the scheduled hours will sum up for me and i can link it to the hours tracker on my master sheet. sorry so detailed, i guess im just being thorough so it's clear what im looking for...since there are probably quite a few ways to do employee schedules. thanks for your help.

re: "columns e thru ab represent every hour of the day. how do i get my entered data in columns a thru c to populate in their respective positions in columns e thru ab." I'm confused as to why you need a column for every hour. Perhaps an explanation of that need would help us get you to a solution.

i have included each hour of the day because my plan is to include a clickable button for each day that will generate a coverage map/graph for me to print. The only way I knew how to have excel do that was by giving each hour of the day it's own value in it's own cell. Then, after all thirteen shifts are scheduled for the day, i'll be able to click and print a graph showing me where the coverage is based on the whole number 1 for each hour, where lunches are based on values of 0.5 and where overlap coverage is. Sounds complicated but I used this exact type of spreadsheet at my previous employer but the darn thing is locked/protected so unfortunately i can't just copy and paste.

Do you have a copy of the locked spreadsheet that I could look at? I may be able to determine how they are doing it if I can see how the spreadsheet works. I can PM you an email address if you'd like. This seemed to work (partially): I started with this: A B C D E F G etc. 1 1:00 AM 2:00 AM 3:00 AM etc. 2 8:00 AM 4:00 PM 12:00 PM In E2 I put this, and dragged it across 24 cells and down a few rows: =IF(HOUR(E$1)=HOUR($C2),0.5,IF(AND(E$1>=$A2,E$1<=$B2),1,"")) I get the 1 and .5 where I think you want them, but it doesn't work if the Start time is PM and the End time is AM. That impacts the overnight shifts. I'm sure there's a way to do it but I'd need to work on it a bit.

i can definitely send you a copy of the locked spreadsheet. email me at osu_guy_2004 @ yahoo . com