Use start and stop times (or length) to fill cells in between for schedules

[soccer4ard]

Hello,

I am trying to figure out how to limit my workload a bit. When creating a schedule, I already have start time, end time and, I can easily calculate length of a shift. On another sheet, I have a graphical version of these schedules. Times are filled down in 30 minute increments in column A. The rest of columnscorrespond to employees. To enter employee's schedules into this sheet, I just enter 1 into a cell that corresponds with the employee's name and start time and drag that cell down until the end of their shift time.

Question is...can excel look up that employee's start time and enter 1 into cells until the end time (or down to match the length of the shift). In other words, John Smith works from 8:00 AM to 5:00 PM. Excel would need to find John Smith column. Then, it would go down to row corresponding with 8:00 AM and enter 1 into it. It would continue entering 1 into each cell in the same column until either length of shift condition is met or until it reaches the row corresponding to the end of shift time.

Let me know if that makes sense. I'm not sure if there is a formula for this or if I should have posted this into VBA thread because it's a more of a programming thing?

Any help/advice is appreciated!!

[Pete_UK]

I think you could probably do this using formulae, but it would help if you attached a sample Excel workbook so we can see how your data is arranged.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Pete

[Tony Valko]

Do the shifts span past midnight?

Data Range

	A	B	C	D	E	F	G
1		Bill	Lisa		Employee	Start	End
2	8:00 AM	1			Bill	8:00 AM	12:00 PM
3	8:30 AM	1			Lisa	10:00 AM	3:30 PM
4	9:00 AM	1
5	9:30 AM	1
6	10:00 AM	1	1
7	10:30 AM	1	1
8	11:00 AM	1	1
9	11:30 AM	1	1
10	12:00 PM	1	1
11	12:30 PM		1
12	1:00 PM		1
13	1:30 PM		1
14	2:00 PM		1
15	2:30 PM		1
16	3:00 PM		1
17	3:30 PM		1
18	4:00 PM
19	4:30 PM
20	5:00 PM
21	5:30 PM
22	6:00 PM
23	6:30 PM
24	7:00 PM