1st and 15th pay period calculation

[sdickey_2000]

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!!

[DonkeyOte]

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:

Sheet3

	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

Spreadsheet Formulas

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.