Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old November 11th 08, 09:14 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2007
Posts: 6
Default create excel spreadsheet for weekly work

I barely know excel. I have been asked to create a spreadsheet that will
rotate 8 people for an entire year. I think it can be done, but need to know
where to begin. I woudl also like this to be dynamic. If I input a new YEAR
then I want all the fields to adjust accordingly.

So for the first week of 2009 person A
for the second week of 2009 person b
and so on for a total of 8 people

The pattern is then to repeat for the whole year. This is an oncall list.

I have been looking at various calender templates and not really seeing a
starting point.

Any help is appreciate.

--
Lizette Koehler

  #2   Report Post  
Old November 13th 08, 05:26 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2007
Posts: 168
Default create excel spreadsheet for weekly work

This should help but will involve a complicated formula and a macro.

To start, let's enter the year in cell B2. You can even change the font &
color of that cell so you know it is an input field.

We'll also list all staff members in column A beginning with A5 (I assume
you'll want a title and a header of some sort in the first few rows). B5
will begin listing the dates which start each week and go down 52 rows to the
end of the year. I have used Monday as the start date for each week. C5
through C56 will then hold your on-call assignments.

Okay, so in B5 we'll have the first Monday for the week containing 1/1 of
the year in B2. B5 needs this formula (sorry, I don't understand the
formula, it came from MVP Ron de Bruin in the Netherlands, so I don't know
how to change it from Mondays to another day):
=IF(ISERROR(DATE(1900,1,1)),IF(B2=1904,DATE(1904,1 ,4),DATE(B2,1,0)-MOD(DATE(B2,1,0)-2,7)+(7*(MOD(DATE(B2,1,0)-2,7)3))+1),IF(B2=1900,DATE(1900,1,1),DATE(B2,1,1)-MOD(DATE(B2,1,1)-2,7)+(7*(MOD(DATE(B2,1,1)-2,7)3))))

Then select B6:B56 and type in this formula:
=B5+7
and commit using Ctrl+Enter.

Now B5:B56 should show the Monday which starts a new week in chronological
order for 52 weeks, and A5:A12 should have your staff member names or codes
in the order you'd like them to be. Now comes the macro.

Right-click the sheet tab and select "View Code". What we want is a macro
which will update the on-call list when you change the year or add/remove an
employee. The macro will reevaluate the entire year every time, so it's not
"smart" enough to only reassign the remainder of the year if there's a staff
change, and it does not accommodate handling people who are not available for
a particular week...but for the most part it should do what you want. Here's
what you'll paste into the VBA Editor which popped up on your screen:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim staff As Long
Dim i As Long, j As Integer
staff = Cells(Rows.Count, 1).End(xlUp).Row
j = 5
For i = 5 To 56
Cells(i, "C").Value = Cells(j, "A").Value
If j + 1 staff Then j = 5 Else j = j + 1
Next i
End Sub

Just close the window or press Alt+Q to exit the VBA Editor.

Now you have an on-call list which assigns each staff member an on-call week
in the same order throughout the year, and whenever you add or remove a staff
member or change the year in B2, the entire table will reset. If it's the
same year and you need to either add or remove an employee, you can tweak the
macro at this line:
For i = 5 To 56
so that instead of starting in row 5 perhaps it starts at the week you
really want (say the week shown in row 28)...all you'd do is change the line
above so that 5 is 28. Now go back to your sheet and change your staff list
accordingly and it will only reassign the on-call list beginning with the
week you instructed it to. Just don't forget to change the macro back to 5
when you're done.

Hope this helps.
--
Please remember to indicate when the post is answered so others can benefit
from it later.


"Lizette Koehler" wrote:

I barely know excel. I have been asked to create a spreadsheet that will
rotate 8 people for an entire year. I think it can be done, but need to know
where to begin. I woudl also like this to be dynamic. If I input a new YEAR
then I want all the fields to adjust accordingly.

So for the first week of 2009 person A
for the second week of 2009 person b
and so on for a total of 8 people

The pattern is then to repeat for the whole year. This is an oncall list.

I have been looking at various calender templates and not really seeing a
starting point.

Any help is appreciate.

--
Lizette Koehler



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 daily plan in Excel for tasks of weekly, monthly frequency Sukhatma Excel Discussion (Misc queries) 2 December 18th 07 04:54 PM
Creating a weekly rotating employee work schedule deana06262 Excel Discussion (Misc queries) 0 November 12th 07 06:29 PM
I am trying to create a timesheet to work out weekly flexi time. Ranger Nige Excel Discussion (Misc queries) 2 July 31st 07 10:13 AM
Create Excel Template- Weekly Appt. Schedule by 15-min increments navykappakitten75 Excel Discussion (Misc queries) 0 September 8th 06 02:46 PM
Does anyone have a weekly schedule that computes total hours work Guillermo Excel Worksheet Functions 0 April 3rd 06 06:03 AM


All times are GMT +1. The time now is 07:01 PM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017