Help me

[Guest]

on sheet represents one employee and the days he works and his productivity.
the dates of work are in column A row 1 down to 16 or 17 or so, and in
column B row 1 is the month that the productivity took place. On sheet two
lists all the months for several years with the dates listed under the month.
For example January-06 is in A1 and under that from A2 down to A16 or A17 or
so, list the date in each cell for example A2 is 1, A3 is ,2 etc. and B1 is
February-06 with its dates listed in B2 down to B16 or B17 or so. Let's go
back to sheet one and what I want to do is if the month in col B row 1 is
let's say January-06 then I want the respectives dates listed under
January-06 on sheet two to be placed in col A1 down to A16 or 17 or so
(depending on how many workdays an employee is scheduled that month) is
something like this possible and if so how?

Employees work four days on and then four days off, so it becomes hard each
time I start a new sheet for a month for me to go down column A and manually
type in their first day of work in a month which might be the 3rd for example
and then count 4,5,6 11,12,13,14, 19,20,21,22, 27,28,29,30. So I did it
once on sheet two for 3 years worth of scheduling and I am looking to now
have each set of days in a work month exported to each employees sheet in
Column A rows 1 down to 16 or 17.

Thank You,
brian

 

[Bryan Hessey]

The attached file shows a method of calculating and extracting a single
month of working days under your 4-on 4-off system, commencing from the
2nd April. You can amend the first 4 dates to suit your needs, the rest
should be automatic. (to go beyond 2012 just drag the formula in A and
B further down)

The format works if, on another sheet you enter,
in C1 a first of the month date,
in D1 =MATCH(C1,Sheet2!B:B,0)
in E1 or A1

=IF(MONTH(OFFSET(Sheet2!A$1,D$1-2+ROW(),0))<>MONTH(C$1),"",OFFSET(Sheet2!A$1,D$1-2+ROW(),0))

and drag down to row 16
(? 17 is not possible?)

I presume that you can adjust this to your needs.

attached:
http://www.excelforum.com/attachment.php?attachmentid=4656&d=1145410120

--

on sheet represents one employee and the days he works and his
productivity.
the dates of work are in column A row 1 down to 16 or 17 or so, and
in
column B row 1 is the month that the productivity took place. On sheet
two
lists all the months for several years with the dates listed under the
month.
For example January-06 is in A1 and under that from A2 down to A16 or
A17 or
so, list the date in each cell for example A2 is 1, A3 is ,2 etc. and
B1 is
February-06 with its dates listed in B2 down to B16 or B17 or so.
Let's go
back to sheet one and what I want to do is if the month in col B row 1
is
let's say January-06 then I want the respectives dates listed under
January-06 on sheet two to be placed in col A1 down to A16 or 17 or so
(depending on how many workdays an employee is scheduled that month)
is
something like this possible and if so how?

Employees work four days on and then four days off, so it becomes hard
each
time I start a new sheet for a month for me to go down column A and
manually
type in their first day of work in a month which might be the 3rd for
example
and then count 4,5,6 11,12,13,14, 19,20,21,22, 27,28,29,30. So I
did it
once on sheet two for 3 years worth of scheduling and I am looking to
now
have each set of days in a work month exported to each employees sheet
in
Column A rows 1 down to 16 or 17.

Thank You,
brian

+-------------------------------------------------------------------+
|Filename: 8Day.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4656 |
+-------------------------------------------------------------------+

 

[Bryan Hessey]

well, (after reading your question) - - - To start at A2, use

=IF(MONTH(OFFSET(A$1,D$1-3+ROW(),0))<>MONTH(C$1),"",OFFSET(A$1,D$1-3+ROW(),0))

and drag to row 17

--

 

[Guest]

thank you,
I'll give it a shot.

well, (after reading your question) - - - To start at A2, use

=IF(MONTH(OFFSET(A$1,D$1-3+ROW(),0))<>MONTH(C$1),"",OFFSET(A$1,D$1-3+ROW(),0))

and drag to row 17

 

[Guest]

It worked, it is awsome, thank you so much.
brian

 

[Bryan Hessey]

Good to see, and thanks for the response.

--

It worked, it is awsome, thank you so much.
brian