Divide month into two payroll periods

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

craezer - 19 Mar 2008 00:55 GMT

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.

Reply to this Message

Max - 19 Mar 2008 02:49 GMT

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

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> 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.
[quoted text clipped - 12 lines]
> the pay period, whether it starts on the 1st or 16th, without extra dates or
> blank rows.

Reply to this Message

Rick Rothstein (MVP - VB) - 19 Mar 2008 08:26 GMT

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

>I am trying to display rows by pay period. The dates would be from the 1st
>to
[quoted text clipped - 16 lines]
> or
> blank rows.

Reply to this Message

craezer - 21 Mar 2008 03:13 GMT

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!

> Assuming the only dates that will ever go in A11 are the 1st or the 16th of
> a given month, try putting this formula...
[quoted text clipped - 25 lines]
> > or
> > blank rows.

Reply to this Message

Rick Rothstein (MVP - VB) - 21 Mar 2008 04:39 GMT

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

> Thanks, Rick, that did the trick!
>
[quoted text clipped - 48 lines]
>> > or
>> > blank rows.

Reply to this Message

craezer - 21 Mar 2008 08:06 GMT

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

> 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
[quoted text clipped - 56 lines]
> >> > or
> >> > blank rows.

Reply to this Message