Hello all,

I am fairly new at using Excel and have found various ways to get done what
I want to get done and am learning quite a bit from trial and error.
However, I am stuck on something and not really sure if it is possible to
fix. Any help is appreciated!

I have created a schedule maker that is linked to a calendar (same file
different worksheet), so that when I input in and out times, it automatically
enters these figures in the proper calendar location. However, in order to
get those times, I enter €œ1€� for each half hour worked on a timescale from
7:30 am to 7:00 pm in half hour increments. My formula also includes
calculations for 15 minutes before and after each shift for set up, so, an
employee working from 8am to 11am is actually on the clock for 3 ½ hours
instead of three. Everything works great, but my problem is, I have to
manually input the actual clock times into a separate box so it will transfer
to the calendar.

My spreadsheet looks something like this: (The blank space indicates a lunch
break)
Name 7:30 - 8:00 - 8:30 - 9:00 - 9:30 - 10:00 - 10:30 - 11:00
Bob 1 1 1 1 1
1
Stan 1 1 1
1

The 1s are automatically added into another box which calculates the hours
worked and in a different box I manually type in €œBob 7:30 €“ 11:00€� and €œStan
8:30 €“ 11:00€� This information is what transfers to the calendar. So, what I
want to know is, is it possible to only have to enter the €œ1€� in the time
slots and have Excel calculate the hours working, without having to do an in
and an out for lunch, and automatically update the calendar without having to
type in the €œ7:30€�??? I am not looking for a way to calculate the total hours
worked, rather I am trying to have Excel recognize where the €œ1s€� start and
finish on the timescale and automatically transfer those in and out times to
the calendar. Does this make any sense to anyone??? If it is possible,
without having to rewrite the entire spreadsheet, please share! Thanks!

Oops...sorry, the example got cut off...It should look more like this:


My spreadsheet looks something like this: (The blank space indicates a lunch
break)
Name 7:30 - 8:00 - 8:30 - 9:00 - 9:30 - 10:00 - 10:30 - 11:00
Bob 1 1 1 1 1
1
Stan 1 1 1
1