software

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


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))lt;gt;MONTH(C$1),quot;quot;,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:
www.excelforum.com/attachment...6amp;d=1145410120

--

Brian Wrote:
gt; on sheet represents one employee and the days he works and his
gt; productivity.
gt; the dates of work are in column A row 1 down to 16 or 17 or so, and
gt; in
gt; column B row 1 is the month that the productivity took place. On sheet
gt; two
gt; lists all the months for several years with the dates listed under the
gt; month.
gt; For example January-06 is in A1 and under that from A2 down to A16 or
gt; A17 or
gt; so, list the date in each cell for example A2 is 1, A3 is ,2 etc. and
gt; B1 is
gt; February-06 with its dates listed in B2 down to B16 or B17 or so.
gt; Let's go
gt; back to sheet one and what I want to do is if the month in col B row 1
gt; is
gt; let's say January-06 then I want the respectives dates listed under
gt; January-06 on sheet two to be placed in col A1 down to A16 or 17 or so
gt; (depending on how many workdays an employee is scheduled that month)
gt; is
gt; something like this possible and if so how?
gt;
gt; Employees work four days on and then four days off, so it becomes hard
gt; each
gt; time I start a new sheet for a month for me to go down column A and
gt; manually
gt; type in their first day of work in a month which might be the 3rd for
gt; example
gt; and then count 4,5,6 11,12,13,14, 19,20,21,22, 27,28,29,30. So I
gt; did it
gt; once on sheet two for 3 years worth of scheduling and I am looking to
gt; now
gt; have each set of days in a work month exported to each employees sheet
gt; in
gt; Column A rows 1 down to 16 or 17.
gt;
gt; Thank You,
gt; brian -------------------------------------------------------------------
|Filename: 8Day.zip |
|Download: www.excelforum.com/attachment.php?postid=4656 |
-------------------------------------------------------------------

--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: www.excelforum.com/member.php...oamp;userid=21059
View this thread: www.excelforum.com/showthread...hreadid=533965
well, (after reading your question) - - - To start at A2, use

=IF(MONTH(OFFSET(A$1,D$1-3 ROW(),0))lt;gt;MONTH(C$1),quot;quot;,OFFSET(A$1,D$1-3 ROW(),0))

and drag to row 17

--

Bryan Hessey Wrote:
gt; The attached file shows a method of calculating and extracting a single
gt; month of working days under your 4-on 4-off system, commencing from the
gt; 2nd April. You can amend the first 4 dates to suit your needs, the rest
gt; should be automatic. (to go beyond 2012 just drag the formula in A and
gt; B further down)
gt;
gt; The format works if, on another sheet you enter,
gt; in C1 a first of the month date,
gt; in D1 =MATCH(C1,Sheet2!B:B,0)
gt; in E1 or A1
gt;
gt; =IF(MONTH(OFFSET(Sheet2!A$1,D$1-2 ROW(),0))lt;gt;MONTH(C$1),quot;quot;,OFFSET(Sheet2!A$1,D$1-2 ROW(),0))
gt;
gt; and drag down to row 16
gt; (? 17 is not possible?)
gt;
gt; I presume that you can adjust this to your needs.
gt;
gt; attached:
gt; www.excelforum.com/attachment...6amp;d=1145410120
gt;
gt; ----
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: www.excelforum.com/member.php...oamp;userid=21059
View this thread: www.excelforum.com/showthread...hreadid=533965thank you,
I'll give it a shot.

quot;Bryan Hesseyquot; wrote:

gt;
gt; well, (after reading your question) - - - To start at A2, use
gt;
gt; =IF(MONTH(OFFSET(A$1,D$1-3 ROW(),0))lt;gt;MONTH(C$1),quot;quot;,OFFSET(A$1,D$1-3 ROW(),0))
gt;
gt; and drag to row 17
gt;
gt; --
gt;
gt; Bryan Hessey Wrote:
gt; gt; The attached file shows a method of calculating and extracting a single
gt; gt; month of working days under your 4-on 4-off system, commencing from the
gt; gt; 2nd April. You can amend the first 4 dates to suit your needs, the rest
gt; gt; should be automatic. (to go beyond 2012 just drag the formula in A and
gt; gt; B further down)
gt; gt;
gt; gt; The format works if, on another sheet you enter,
gt; gt; in C1 a first of the month date,
gt; gt; in D1 =MATCH(C1,Sheet2!B:B,0)
gt; gt; in E1 or A1
gt; gt;
gt; gt; =IF(MONTH(OFFSET(Sheet2!A$1,D$1-2 ROW(),0))lt;gt;MONTH(C$1),quot;quot;,OFFSET(Sheet2!A$1,D$1-2 ROW(),0))
gt; gt;
gt; gt; and drag down to row 16
gt; gt; (? 17 is not possible?)
gt; gt;
gt; gt; I presume that you can adjust this to your needs.
gt; gt;
gt; gt; attached:
gt; gt; www.excelforum.com/attachment...6amp;d=1145410120
gt; gt;
gt; gt; --
gt;
gt;
gt; --
gt; Bryan Hessey
gt; ------------------------------------------------------------------------
gt; Bryan Hessey's Profile: www.excelforum.com/member.php...oamp;userid=21059
gt; View this thread: www.excelforum.com/showthread...hreadid=533965
gt;
gt;

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

quot;Bryan Hesseyquot; wrote:

gt;
gt; well, (after reading your question) - - - To start at A2, use
gt;
gt; =IF(MONTH(OFFSET(A$1,D$1-3 ROW(),0))lt;gt;MONTH(C$1),quot;quot;,OFFSET(A$1,D$1-3 ROW(),0))
gt;
gt; and drag to row 17
gt;
gt; --
gt;
gt; Bryan Hessey Wrote:
gt; gt; The attached file shows a method of calculating and extracting a single
gt; gt; month of working days under your 4-on 4-off system, commencing from the
gt; gt; 2nd April. You can amend the first 4 dates to suit your needs, the rest
gt; gt; should be automatic. (to go beyond 2012 just drag the formula in A and
gt; gt; B further down)
gt; gt;
gt; gt; The format works if, on another sheet you enter,
gt; gt; in C1 a first of the month date,
gt; gt; in D1 =MATCH(C1,Sheet2!B:B,0)
gt; gt; in E1 or A1
gt; gt;
gt; gt; =IF(MONTH(OFFSET(Sheet2!A$1,D$1-2 ROW(),0))lt;gt;MONTH(C$1),quot;quot;,OFFSET(Sheet2!A$1,D$1-2 ROW(),0))
gt; gt;
gt; gt; and drag down to row 16
gt; gt; (? 17 is not possible?)
gt; gt;
gt; gt; I presume that you can adjust this to your needs.
gt; gt;
gt; gt; attached:
gt; gt; www.excelforum.com/attachment...6amp;d=1145410120
gt; gt;
gt; gt; --
gt;
gt;
gt; --
gt; Bryan Hessey
gt; ------------------------------------------------------------------------
gt; Bryan Hessey's Profile: www.excelforum.com/member.php...oamp;userid=21059
gt; View this thread: www.excelforum.com/showthread...hreadid=533965
gt;
gt;


Good to see, and thanks for the response.

--

Brian Wrote:
gt; It worked, it is awsome, thank you so much.
gt; brian
gt;
gt; quot;Bryan Hesseyquot; wrote:
gt;
gt; gt;
gt; gt; well, (after reading your question) - - - To start at A2, use
gt; gt;
gt; gt;
gt; =IF(MONTH(OFFSET(A$1,D$1-3 ROW(),0))lt;gt;MONTH(C$1),quot;quot;,OFFSET(A$1,D$1-3 ROW(),0))
gt; gt;
gt; gt; and drag to row 17
gt; gt;
gt; gt; --
gt; gt;
gt; gt; Bryan Hessey Wrote:
gt; gt; gt; The attached file shows a method of calculating and extracting a
gt; single
gt; gt; gt; month of working days under your 4-on 4-off system, commencing from
gt; the
gt; gt; gt; 2nd April. You can amend the first 4 dates to suit your needs, the
gt; rest
gt; gt; gt; should be automatic. (to go beyond 2012 just drag the formula in A
gt; and
gt; gt; gt; B further down)
gt; gt; gt;
gt; gt; gt; The format works if, on another sheet you enter,
gt; gt; gt; in C1 a first of the month date,
gt; gt; gt; in D1 =MATCH(C1,Sheet2!B:B,0)
gt; gt; gt; in E1 or A1
gt; gt; gt;
gt; gt; gt;
gt; =IF(MONTH(OFFSET(Sheet2!A$1,D$1-2 ROW(),0))lt;gt;MONTH(C$1),quot;quot;,OFFSET(Sheet2!A$1,D$1-2 ROW(),0))
gt; gt; gt;
gt; gt; gt; and drag down to row 16
gt; gt; gt; (? 17 is not possible?)
gt; gt; gt;
gt; gt; gt; I presume that you can adjust this to your needs.
gt; gt; gt;
gt; gt; gt; attached:
gt; gt; gt;
gt; www.excelforum.com/attachment...6amp;d=1145410120
gt; gt; gt;
gt; gt; gt; --
gt; gt;
gt; gt;
gt; gt; --
gt; gt; Bryan Hessey
gt; gt;
gt; ------------------------------------------------------------------------
gt; gt; Bryan Hessey's Profile:
gt; www.excelforum.com/member.php...oamp;userid=21059
gt; gt; View this thread:
gt; www.excelforum.com/showthread...hreadid=533965
gt; gt;
gt; gt;--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: www.excelforum.com/member.php...oamp;userid=21059
View this thread: www.excelforum.com/showthread...hreadid=533965