Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to determine the number of month between 2 periods? | Excel Discussion (Misc queries) | |||
How to determine the number of month between 2 periods? | Excel Worksheet Functions | |||
Adding payroll stubs payroll calculator | New Users to Excel | |||
calculating number of three month periods between two dates... | Excel Discussion (Misc queries) | |||
working out quarters (three-month periods) between two dates | Excel Worksheet Functions |