Divide month into two payroll periods

[craezer]

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.

 

[Max]

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 A22, 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

 

[Rick Rothstein \(MVP - VB\)]

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]

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\)]

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(A11)=1))

Rick

 

[craezer]

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