Divide month into two payroll periods
 

I am trying to display rows by pay period. The dates would be from the 1st to
the 15th or 16th to the end of the month.

I am using a calendar control to select the start date. The calendar control
inserts the pay period start date into A11. Then
A12 is =(A11+1)*(MONTH(A11+1)=MONTH($A$11))
A13 is =(A12+1)*(MONTH(A12+1)=MONTH($A$11))
and so on.

If I select the 16th for the start, it correctly shows rows for the 16th to
the month end except for numerous blank rows after the last date. But if I
select the first pay period, the entire month displays.

What I am trying to accomplish is to display only the neccessary rows for
the pay period, whether it starts on the 1st or 16th, without extra dates or
blank rows.

Divide month into two payroll periods
 

Here's one simple formulas play to split it into 2 separate sheets
automatically, as desired ..

Illustrated in this sample:
http://www.freefilehosting.net/download/3dj6m
Splitting payroll into separate shts by pay period.xls

Source data assumed in sheet: M, cols A to C, from row2 down
where col A contains real dates

In a new sheet, named: 1st (say, for the 1st pay period: 1-15th)
In A2:
=IF(M!A2="","",IF(DAY(M!A2)<=15,ROW(),""))
Leave A1 blank

In B2:
=IF(ROWS($1:1)COUNT($A:$A),"",INDEX(M!A:A,SMALL($ A:$A,ROWS($1:1))))
Copy B2 to D2. Select A2:D2, copy down to cover the max expected extent of
source data, say down to D200? Format col B as date. Minimize/hide away col
A. Cols B to D will return only the lines from M where the dates are between
1-15th. Dress it up nicely to suit.

Then just make a copy of "1st", name it as: 2nd (say, for the 2nd pay
period: 15th)
Amend the formula in A2 to:
=IF(M!A2="","",IF(DAY(M!A2)15,ROW(),""))
Copy A2 down, and you'd get the desired results for the 2nd pay period: 15th
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"craezer" wrote:
I am trying to display rows by pay period. The dates would be from the 1st to
the 15th or 16th to the end of the month.

I am using a calendar control to select the start date. The calendar control
inserts the pay period start date into A11. Then
A12 is =(A11+1)*(MONTH(A11+1)=MONTH($A$11))
A13 is =(A12+1)*(MONTH(A12+1)=MONTH($A$11))
and so on.

If I select the 16th for the start, it correctly shows rows for the 16th to
the month end except for numerous blank rows after the last date. But if I
select the first pay period, the entire month displays.

What I am trying to accomplish is to display only the neccessary rows for
the pay period, whether it starts on the 1st or 16th, without extra dates or
blank rows.

Divide month into two payroll periods
 

Assuming the only dates that will ever go in A11 are the 1st or the 16th of
a given month, try putting this formula...

=IF(AND(DAY(N(A11)+1)DAY($A$11),DAY(N(A11))<15), A11+1,"")

in A12 and copy it down to A26.

Rick


"craezer" wrote in message
...
I am trying to display rows by pay period. The dates would be from the 1st
to
the 15th or 16th to the end of the month.

I am using a calendar control to select the start date. The calendar
control
inserts the pay period start date into A11. Then
A12 is =(A11+1)*(MONTH(A11+1)=MONTH($A$11))
A13 is =(A12+1)*(MONTH(A12+1)=MONTH($A$11))
and so on.

If I select the 16th for the start, it correctly shows rows for the 16th
to
the month end except for numerous blank rows after the last date. But if I
select the first pay period, the entire month displays.

What I am trying to accomplish is to display only the neccessary rows for
the pay period, whether it starts on the 1st or 16th, without extra dates
or
blank rows.

Divide month into two payroll periods
 

Thanks, Rick, that did the trick!

One more question. There are two cells that have the pay period starting and
ending dates, "From" and "To". Is there a way to have the dates entered into
those cells as well? The first period seems to be easy enough. If I select
the 1st, the 15th pops into the "To" cell. It's the second period that has me
stumped. Easy enough to get the 16th in the "From" cell, but I need the last
day of the month in the "To" cell and I don't know what the formula is to get
that.

Thanks again!

"Rick Rothstein (MVP - VB)" wrote:

Assuming the only dates that will ever go in A11 are the 1st or the 16th of
a given month, try putting this formula...

=IF(AND(DAY(N(A11)+1)DAY($A$11),DAY(N(A11))<15), A11+1,"")

in A12 and copy it down to A26.

Rick


"craezer" wrote in message
...
I am trying to display rows by pay period. The dates would be from the 1st
to
the 15th or 16th to the end of the month.

I am using a calendar control to select the start date. The calendar
control
inserts the pay period start date into A11. Then
A12 is =(A11+1)*(MONTH(A11+1)=MONTH($A$11))
A13 is =(A12+1)*(MONTH(A12+1)=MONTH($A$11))
and so on.

If I select the 16th for the start, it correctly shows rows for the 16th
to
the month end except for numerous blank rows after the last date. But if I
select the first pay period, the entire month displays.

What I am trying to accomplish is to display only the neccessary rows for
the pay period, whether it starts on the 1st or 16th, without extra dates
or
blank rows.

Divide month into two payroll periods
 

I'm assuming A11 is in your "From" column (so it will contain a date that is
either the 1st or the 16th of the month). If so, and assuming B11 is in your
"To" column, put this formula in B11...

=DATE(YEAR(A11),MONTH(A11)+(DAY(A11)=16),15*(DAY(A 11)=1))

Rick


"craezer" wrote in message
...
Thanks, Rick, that did the trick!

One more question. There are two cells that have the pay period starting
and
ending dates, "From" and "To". Is there a way to have the dates entered
into
those cells as well? The first period seems to be easy enough. If I select
the 1st, the 15th pops into the "To" cell. It's the second period that has
me
stumped. Easy enough to get the 16th in the "From" cell, but I need the
last
day of the month in the "To" cell and I don't know what the formula is to
get
that.

Thanks again!

"Rick Rothstein (MVP - VB)" wrote:

Assuming the only dates that will ever go in A11 are the 1st or the 16th
of
a given month, try putting this formula...

=IF(AND(DAY(N(A11)+1)DAY($A$11),DAY(N(A11))<15), A11+1,"")

in A12 and copy it down to A26.

Rick


"craezer" wrote in message
...
I am trying to display rows by pay period. The dates would be from the
1st
to
the 15th or 16th to the end of the month.

I am using a calendar control to select the start date. The calendar
control
inserts the pay period start date into A11. Then
A12 is =(A11+1)*(MONTH(A11+1)=MONTH($A$11))
A13 is =(A12+1)*(MONTH(A12+1)=MONTH($A$11))
and so on.

If I select the 16th for the start, it correctly shows rows for the
16th
to
the month end except for numerous blank rows after the last date. But
if I
select the first pay period, the entire month displays.

What I am trying to accomplish is to display only the neccessary rows
for
the pay period, whether it starts on the 1st or 16th, without extra
dates
or
blank rows.

Divide month into two payroll periods
 

You're two for two. Thank you for your help, Rick!

"Rick Rothstein (MVP - VB)" wrote:

I'm assuming A11 is in your "From" column (so it will contain a date that is
either the 1st or the 16th of the month). If so, and assuming B11 is in your
"To" column, put this formula in B11...

=DATE(YEAR(A11),MONTH(A11)+(DAY(A11)=16),15*(DAY(A 11)=1))

Rick


"craezer" wrote in message
...
Thanks, Rick, that did the trick!

One more question. There are two cells that have the pay period starting
and
ending dates, "From" and "To". Is there a way to have the dates entered
into
those cells as well? The first period seems to be easy enough. If I select
the 1st, the 15th pops into the "To" cell. It's the second period that has
me
stumped. Easy enough to get the 16th in the "From" cell, but I need the
last
day of the month in the "To" cell and I don't know what the formula is to
get
that.

Thanks again!

"Rick Rothstein (MVP - VB)" wrote:

Assuming the only dates that will ever go in A11 are the 1st or the 16th
of
a given month, try putting this formula...

=IF(AND(DAY(N(A11)+1)DAY($A$11),DAY(N(A11))<15), A11+1,"")

in A12 and copy it down to A26.

Rick


"craezer" wrote in message
...
I am trying to display rows by pay period. The dates would be from the
1st
to
the 15th or 16th to the end of the month.

I am using a calendar control to select the start date. The calendar
control
inserts the pay period start date into A11. Then
A12 is =(A11+1)*(MONTH(A11+1)=MONTH($A$11))
A13 is =(A12+1)*(MONTH(A12+1)=MONTH($A$11))
and so on.

If I select the 16th for the start, it correctly shows rows for the
16th
to
the month end except for numerous blank rows after the last date. But
if I
select the first pay period, the entire month displays.

What I am trying to accomplish is to display only the neccessary rows
for
the pay period, whether it starts on the 1st or 16th, without extra
dates
or
blank rows.