This is a discussion on 1st and 15th pay period calculation within the Excel Questions forums, part of the Question Forums category; I am working on a payroll schedule that needs to list the 1st and 15th pay periods (ie: 01/15/10, 01/31/10). ...
I am working on a payroll schedule that needs to list the 1st and 15th pay periods (ie: 01/15/10, 01/31/10). I am trying to find a quick way to auto fill the page with these dates for the entire year. I am also need it to only fill weekdays.
Example: May 15th is a Saturday so the actual fill date would be 05/14/10, the next pay date would be 05/31/10, the next 06/15/10, 06/30/10, 07/15/10, 07/30/10, and so on...and so on.
I have done this before, but it has been so many years ago that I cannot remember how I did it. Please help!!
Not clear which version of XL you're using but I would suggest you make use of WORKDAY function which prior to XL2007 requires activation of the Analysis ToolPak add-in via Tools -> Add-ins, at which point:
A 1 2010 2 15-Jan-10 3 29-Jan-10 4 15-Feb-10 5 26-Feb-10 6 15-Mar-10 7 31-Mar-10 8 15-Apr-10 9 30-Apr-10 10 14-May-10 11 31-May-10 12 15-Jun-10 13 30-Jun-10 14 15-Jul-10 15 30-Jul-10 16 13-Aug-10 17 31-Aug-10 18 15-Sep-10 19 30-Sep-10 20 15-Oct-10 21 29-Oct-10 22 15-Nov-10 23 30-Nov-10 24 15-Dec-10 25 31-Dec-10
Cell Formula A2 =WORKDAY(DATE($A$1,1+INT(ROWS(A$2:A2)/2),IF(MOD(ROWS(A$2:A2),2),16,1)),-1)
Excel tables to the web >> Excel Jeanie HTML 4
The value in A1 is manual entry (year).
WORKDAY has a further optional parameter to cater for public holidays (Mon-Fri) - see XL Help for more info.
Does my a$$ look big in this picture ?