Based on another thread regarding a payroll workbook, I am creating a lookup table that has the date in one column, then the pay period that date is in in the next column, and then in the third column the month and the year.

I have a formula that works for the first week and then it goes wrong.

The payroll year started on Jan 16 so that is where my table begins. That pay period is 1/16/2012 - 1/29/2012. Then the next pay period would be 1/30/2012 - 2/12/2012.

This is the formula I have:

=TEXT(A2-WEEKDAY(A2)+2,"mm/dd/yy") & " - " &TEXT( A2-WEEKDAY(A2)+15,"mm/dd/yy")

This is a sample of my table to show how it works and then it doesnt work: Seems only the first week is right, then it looks like its on a weekly period instead of bi-weekly.

Can anyone help me with the formula to correct it to be bi-weekly? Thank you so much in advance.

Date PayrollPeriod Month
1/16/2012 01/16/12 - 01/29/12 Jan-12
1/17/2012 01/16/12 - 01/29/12 Jan-12
1/18/2012 01/16/12 - 01/29/12 Jan-12
1/19/2012 01/16/12 - 01/29/12 Jan-12
1/20/2012 01/16/12 - 01/29/12 Jan-12
1/21/2012 01/16/12 - 01/29/12 Jan-12
1/22/2012 01/23/12 - 02/05/12 Jan-12
1/23/2012 01/23/12 - 02/05/12 Jan-12
1/24/2012 01/23/12 - 02/06/12 Jan-12
1/25/2012 01/23/12 - 02/06/12 Jan-12
1/26/2012 01/23/12 - 02/06/12 Jan-12
1/27/2012 01/23/12 - 02/06/12 Jan-12
1/28/2012 01/23/12 - 02/06/12 Jan-12
1/29/2012 01/30/12 - 02/13/12 Jan-12
1/30/2012 01/30/12 - 02/13/12 Feb-12
1/31/2012 01/30/12 - 02/13/12 Feb-12
2/1/2012 01/30/12 - 02/13/12 Feb-12
2/2/2012 01/30/12 - 02/13/12 Feb-12