Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 17
Default Creating a rotating schedule

I'm new to using Excel, but I was wondering if anyone knows if it is
possible to create (or "automate" might be a better word) the following type
of work schedule:

I'm only looking for a "YES" it's possible, or "NO" it isn't. If I know
that it is, I can figure it out myself.....I think.

I do the scheduling for a public safety dispatch center which runs on a "4
day on, 2 day off" schedule with 3 around the clock shifts. There are 3
"lines", that is, employee group#1 starts with Monday and Tuesday off, group
#2 starts with Wednesday and Thursday off, and group #3 starts with Friday
and Saturday off. This schedule rotates each week so that the following
week gr# 1 is off on Sunday and Monday, etc....

I usually do the whole year out by scheduling one person on each line, then
copying and pasting throughout the rest of the schedule, which is a little
time consuming.

Thanks for any input.


Mike

  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: Creating a rotating schedule

Creating a Rotating Schedule in Excel

1. Enter the dates for the first week of your schedule in the first row.
2. Enter the names of your employees for each shift in the second row.
3. Enter the days off for each group in the third row.
  1. For example, "Off" in cells B3 and C3 for Group 1, "Off" in cells D3 and E3 for Group 2, etc.
4. Use formulas to determine which employee from each group should be working on each day.
  1. For example, in cell B4, use the formula: =IF(B$3="Off","",INDEX(Group1,MATCH(B$1,Days,0)))
  2. Adjust the cell references in the formula to match the new location when copying it across the rest of the schedule.
5. Create named ranges for each group of employees.
  1. Select the range of cells containing the employee names for each group.
  2. Click in the Name Box and type a name for the range.
  3. Repeat for each group.
6. Use conditional formatting to highlight the cells for each group's days off.
  1. Select the range of cells for the first group's days off.
  2. Go to the Home tab and click on Conditional Formatting New Rule.
  3. Choose "Use a formula to determine which cells to format" and enter a formula like =B$3="Off".
  4. Choose a formatting style and click OK.
  5. Repeat for each group's days off.
7. Use the fill handle to copy the entire schedule for the rest of the year.
  1. Select the range of cells containing the first week's schedule.
  2. Drag the fill handle down to fill in the rest of the dates.

Formula:
Good luck
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 146
Default Creating a rotating schedule

I'm sure it's possible. It may be possible to make a master template for use
each year. But I'd have to see what the spread sheet looks like for 3-4
weeks to have an example.

"Michael Slater" wrote in message
. ..
I'm new to using Excel, but I was wondering if anyone knows if it is
possible to create (or "automate" might be a better word) the following
type of work schedule:

I'm only looking for a "YES" it's possible, or "NO" it isn't. If I know
that it is, I can figure it out myself.....I think.

I do the scheduling for a public safety dispatch center which runs on a "4
day on, 2 day off" schedule with 3 around the clock shifts. There are 3
"lines", that is, employee group#1 starts with Monday and Tuesday off,
group #2 starts with Wednesday and Thursday off, and group #3 starts with
Friday and Saturday off. This schedule rotates each week so that the
following week gr# 1 is off on Sunday and Monday, etc....

I usually do the whole year out by scheduling one person on each line,
then copying and pasting throughout the rest of the schedule, which is a
little time consuming.

Thanks for any input.


Mike



  #4   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 17
Default Creating a rotating schedule

You know....I thought about that after I posted the message.

The way the schedule looks now (and I don't know if it helps) is like this:

Col: A = Day of the week
Col: B = Date
Col: C thru Q = MID Shift Dispatcher Initials (arranged by "line" / C-G =
Line #1, Etc)
Col: R = Total # of Dispatchers working that shift

Under each Dispatcher's initials is simply the letter "M" (for MID shift) if
they are working that date, or, the letters "DOR" (Day Off Regular), if they
are on a day off.

Example: Col: C-G (all Line #1), going down, row 2&3 (which corresponds to
Sunday & Monday) would have "DOR" under each set of initials. Rows 4,5,6,7
(corresponding to Tuesday, Wednesday, Thursday & Friday) would show the
letter "M" (indicating they are working those days. Then row 8&9 (Saturday
and Sunday) would indicate "DOR", etc., continuing down utilizing a 4 day on
and 2 day off schedule.

I have a worksheet for each shift set up exactly the same way, substituting
the letters "D" for DAY shift and "E" for EVE shift.

The sad part is, our department has been utilizing this same format for more
than the 24 years I've been there (up until 3 years ago, the schedule was
done by hand on photocopied sheets). I have another 19 years to go until I
can retire with full benefits. I thought I could make my life a little
easier ;)



"Dave Thomas" wrote in message
...
I'm sure it's possible. It may be possible to make a master template for
use each year. But I'd have to see what the spread sheet looks like for
3-4 weeks to have an example.

"Michael Slater" wrote in message
. ..
I'm new to using Excel, but I was wondering if anyone knows if it is
possible to create (or "automate" might be a better word) the following
type of work schedule:

I'm only looking for a "YES" it's possible, or "NO" it isn't. If I know
that it is, I can figure it out myself.....I think.

I do the scheduling for a public safety dispatch center which runs on a
"4 day on, 2 day off" schedule with 3 around the clock shifts. There are
3 "lines", that is, employee group#1 starts with Monday and Tuesday off,
group #2 starts with Wednesday and Thursday off, and group #3 starts with
Friday and Saturday off. This schedule rotates each week so that the
following week gr# 1 is off on Sunday and Monday, etc....

I usually do the whole year out by scheduling one person on each line,
then copying and pasting throughout the rest of the schedule, which is a
little time consuming.

Thanks for any input.


Mike




  #5   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 3,365
Default Creating a rotating schedule

I'm not certain this is what you want, or will work for you or not, but I'll
give it a shot.

Start a calendar manually - make manual entries until you have a 4-day work
period that has a DOR above and below it.

Next, in the cell that would be the DOR at the end of that 4 day period -
and for this example we will say you have this in column C:
A B C
1 DAY DATE S1
2 SUN 7/1/07 M
3 MON 7/2/07 DOR
4 TUE 7/3/07 DOR
5 WED 7/4/07 M
6 THUR 7/5/07 M
7 FRI 7/6/07 M
8 SAT 7/7/07 M
9 SUN 7/8/07

So, in C9 put this formula:
=IF(COUNTIF(C4:C8,"M")=4,"DOR","M")

It should display DOR, and as you extend it down the sheet, it should show 2
DOR's 4 M's, 2 DOR's, 4 Ms, continuing as far as you care to extend it.

Repeat for each of the other groups in columns C:Q?

Note how the formula works: the formula above is in row 9, column C, so we
keep referring to that column, but the first row number (C4) is formula
row-5, and the second part (C8) is formula row -1. That's how you need to
set up the various formulas across the sheet. Part of the key is doing the
manual entry at the beginning until you get a 4-M group with a DOR above and
below it.

Hope this helps some - or maybe I've just misunderstood the whole thing.


"Michael Slater" wrote:

You know....I thought about that after I posted the message.

The way the schedule looks now (and I don't know if it helps) is like this:

Col: A = Day of the week
Col: B = Date
Col: C thru Q = MID Shift Dispatcher Initials (arranged by "line" / C-G =
Line #1, Etc)
Col: R = Total # of Dispatchers working that shift

Under each Dispatcher's initials is simply the letter "M" (for MID shift) if
they are working that date, or, the letters "DOR" (Day Off Regular), if they
are on a day off.

Example: Col: C-G (all Line #1), going down, row 2&3 (which corresponds to
Sunday & Monday) would have "DOR" under each set of initials. Rows 4,5,6,7
(corresponding to Tuesday, Wednesday, Thursday & Friday) would show the
letter "M" (indicating they are working those days. Then row 8&9 (Saturday
and Sunday) would indicate "DOR", etc., continuing down utilizing a 4 day on
and 2 day off schedule.

I have a worksheet for each shift set up exactly the same way, substituting
the letters "D" for DAY shift and "E" for EVE shift.

The sad part is, our department has been utilizing this same format for more
than the 24 years I've been there (up until 3 years ago, the schedule was
done by hand on photocopied sheets). I have another 19 years to go until I
can retire with full benefits. I thought I could make my life a little
easier ;)



"Dave Thomas" wrote in message
...
I'm sure it's possible. It may be possible to make a master template for
use each year. But I'd have to see what the spread sheet looks like for
3-4 weeks to have an example.

"Michael Slater" wrote in message
. ..
I'm new to using Excel, but I was wondering if anyone knows if it is
possible to create (or "automate" might be a better word) the following
type of work schedule:

I'm only looking for a "YES" it's possible, or "NO" it isn't. If I know
that it is, I can figure it out myself.....I think.

I do the scheduling for a public safety dispatch center which runs on a
"4 day on, 2 day off" schedule with 3 around the clock shifts. There are
3 "lines", that is, employee group#1 starts with Monday and Tuesday off,
group #2 starts with Wednesday and Thursday off, and group #3 starts with
Friday and Saturday off. This schedule rotates each week so that the
following week gr# 1 is off on Sunday and Monday, etc....

I usually do the whole year out by scheduling one person on each line,
then copying and pasting throughout the rest of the schedule, which is a
little time consuming.

Thanks for any input.


Mike







  #6   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 17
Default Creating a rotating schedule

Thanks for all your help!

Dave was able to put together exactly what I was looking for. One major
headache solved!


"JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message
...
I'm not certain this is what you want, or will work for you or not, but
I'll
give it a shot.

Start a calendar manually - make manual entries until you have a 4-day
work
period that has a DOR above and below it.

Next, in the cell that would be the DOR at the end of that 4 day period -
and for this example we will say you have this in column C:
A B C
1 DAY DATE S1
2 SUN 7/1/07 M
3 MON 7/2/07 DOR
4 TUE 7/3/07 DOR
5 WED 7/4/07 M
6 THUR 7/5/07 M
7 FRI 7/6/07 M
8 SAT 7/7/07 M
9 SUN 7/8/07

So, in C9 put this formula:
=IF(COUNTIF(C4:C8,"M")=4,"DOR","M")

It should display DOR, and as you extend it down the sheet, it should show
2
DOR's 4 M's, 2 DOR's, 4 Ms, continuing as far as you care to extend it.

Repeat for each of the other groups in columns C:Q?

Note how the formula works: the formula above is in row 9, column C, so we
keep referring to that column, but the first row number (C4) is formula
row-5, and the second part (C8) is formula row -1. That's how you need to
set up the various formulas across the sheet. Part of the key is doing
the
manual entry at the beginning until you get a 4-M group with a DOR above
and
below it.

Hope this helps some - or maybe I've just misunderstood the whole thing.


"Michael Slater" wrote:

You know....I thought about that after I posted the message.

The way the schedule looks now (and I don't know if it helps) is like
this:

Col: A = Day of the week
Col: B = Date
Col: C thru Q = MID Shift Dispatcher Initials (arranged by "line" / C-G =
Line #1, Etc)
Col: R = Total # of Dispatchers working that shift

Under each Dispatcher's initials is simply the letter "M" (for MID shift)
if
they are working that date, or, the letters "DOR" (Day Off Regular), if
they
are on a day off.

Example: Col: C-G (all Line #1), going down, row 2&3 (which corresponds
to
Sunday & Monday) would have "DOR" under each set of initials. Rows
4,5,6,7
(corresponding to Tuesday, Wednesday, Thursday & Friday) would show the
letter "M" (indicating they are working those days. Then row 8&9
(Saturday
and Sunday) would indicate "DOR", etc., continuing down utilizing a 4 day
on
and 2 day off schedule.

I have a worksheet for each shift set up exactly the same way,
substituting
the letters "D" for DAY shift and "E" for EVE shift.

The sad part is, our department has been utilizing this same format for
more
than the 24 years I've been there (up until 3 years ago, the schedule was
done by hand on photocopied sheets). I have another 19 years to go until
I
can retire with full benefits. I thought I could make my life a little
easier ;)



"Dave Thomas" wrote in message
...
I'm sure it's possible. It may be possible to make a master template
for
use each year. But I'd have to see what the spread sheet looks like for
3-4 weeks to have an example.

"Michael Slater" wrote in message
. ..
I'm new to using Excel, but I was wondering if anyone knows if it is
possible to create (or "automate" might be a better word) the
following
type of work schedule:

I'm only looking for a "YES" it's possible, or "NO" it isn't. If I
know
that it is, I can figure it out myself.....I think.

I do the scheduling for a public safety dispatch center which runs on
a
"4 day on, 2 day off" schedule with 3 around the clock shifts. There
are
3 "lines", that is, employee group#1 starts with Monday and Tuesday
off,
group #2 starts with Wednesday and Thursday off, and group #3 starts
with
Friday and Saturday off. This schedule rotates each week so that the
following week gr# 1 is off on Sunday and Monday, etc....

I usually do the whole year out by scheduling one person on each line,
then copying and pasting throughout the rest of the schedule, which is
a
little time consuming.

Thanks for any input.


Mike





  #7   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 3,365
Default Creating a rotating schedule

Good to hear that you got it working - now you can relax for the next 19
years while telling everyone just how hard dealing with their scheduling
really is! <g

"Michael Slater" wrote:

Thanks for all your help!

Dave was able to put together exactly what I was looking for. One major
headache solved!


"JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message
...
I'm not certain this is what you want, or will work for you or not, but
I'll
give it a shot.

Start a calendar manually - make manual entries until you have a 4-day
work
period that has a DOR above and below it.

Next, in the cell that would be the DOR at the end of that 4 day period -
and for this example we will say you have this in column C:
A B C
1 DAY DATE S1
2 SUN 7/1/07 M
3 MON 7/2/07 DOR
4 TUE 7/3/07 DOR
5 WED 7/4/07 M
6 THUR 7/5/07 M
7 FRI 7/6/07 M
8 SAT 7/7/07 M
9 SUN 7/8/07

So, in C9 put this formula:
=IF(COUNTIF(C4:C8,"M")=4,"DOR","M")

It should display DOR, and as you extend it down the sheet, it should show
2
DOR's 4 M's, 2 DOR's, 4 Ms, continuing as far as you care to extend it.

Repeat for each of the other groups in columns C:Q?

Note how the formula works: the formula above is in row 9, column C, so we
keep referring to that column, but the first row number (C4) is formula
row-5, and the second part (C8) is formula row -1. That's how you need to
set up the various formulas across the sheet. Part of the key is doing
the
manual entry at the beginning until you get a 4-M group with a DOR above
and
below it.

Hope this helps some - or maybe I've just misunderstood the whole thing.


"Michael Slater" wrote:

You know....I thought about that after I posted the message.

The way the schedule looks now (and I don't know if it helps) is like
this:

Col: A = Day of the week
Col: B = Date
Col: C thru Q = MID Shift Dispatcher Initials (arranged by "line" / C-G =
Line #1, Etc)
Col: R = Total # of Dispatchers working that shift

Under each Dispatcher's initials is simply the letter "M" (for MID shift)
if
they are working that date, or, the letters "DOR" (Day Off Regular), if
they
are on a day off.

Example: Col: C-G (all Line #1), going down, row 2&3 (which corresponds
to
Sunday & Monday) would have "DOR" under each set of initials. Rows
4,5,6,7
(corresponding to Tuesday, Wednesday, Thursday & Friday) would show the
letter "M" (indicating they are working those days. Then row 8&9
(Saturday
and Sunday) would indicate "DOR", etc., continuing down utilizing a 4 day
on
and 2 day off schedule.

I have a worksheet for each shift set up exactly the same way,
substituting
the letters "D" for DAY shift and "E" for EVE shift.

The sad part is, our department has been utilizing this same format for
more
than the 24 years I've been there (up until 3 years ago, the schedule was
done by hand on photocopied sheets). I have another 19 years to go until
I
can retire with full benefits. I thought I could make my life a little
easier ;)



"Dave Thomas" wrote in message
...
I'm sure it's possible. It may be possible to make a master template
for
use each year. But I'd have to see what the spread sheet looks like for
3-4 weeks to have an example.

"Michael Slater" wrote in message
. ..
I'm new to using Excel, but I was wondering if anyone knows if it is
possible to create (or "automate" might be a better word) the
following
type of work schedule:

I'm only looking for a "YES" it's possible, or "NO" it isn't. If I
know
that it is, I can figure it out myself.....I think.

I do the scheduling for a public safety dispatch center which runs on
a
"4 day on, 2 day off" schedule with 3 around the clock shifts. There
are
3 "lines", that is, employee group#1 starts with Monday and Tuesday
off,
group #2 starts with Wednesday and Thursday off, and group #3 starts
with
Friday and Saturday off. This schedule rotates each week so that the
following week gr# 1 is off on Sunday and Monday, etc....

I usually do the whole year out by scheduling one person on each line,
then copying and pasting throughout the rest of the schedule, which is
a
little time consuming.

Thanks for any input.


Mike






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
create work schedule in Excell (6employees rotating shifts) suzyg23 Excel Worksheet Functions 1 December 9th 06 08:21 PM
Creating a schedule cecarter74 Excel Discussion (Misc queries) 1 June 13th 06 11:59 PM
Creating a Schedule will Excel Discussion (Misc queries) 0 December 15th 05 03:26 PM
Creating a schedule edc Excel Worksheet Functions 3 December 9th 05 05:55 PM
rotating 12 hour schedule shared with 3, one weekend off monthly koalabusdriver Excel Discussion (Misc queries) 1 March 1st 05 03:17 PM


All times are GMT +1. The time now is 06:16 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"