Calculating beginning and end of bi weekly pay periods for lookup table

**tilioso** · 03-13-2012, 01:35 PM

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

**Richard Buttrey** · 03-13-2012, 01:46 PM

Hi,

You've shown us the incorrect results. You need to show us what you expect to see. Please upload your workbook and manually add the results.

By bi-weekly you seem to be suggesting that each week the payroll period should jump forward two weeks. But doesn't that mean that the payroll period gets further in further in advance of the actual date?

You may not in fact need a lookup table since I guess that everything can be calculated from the date given.

Regards

**tilioso** · 03-13-2012, 02:09 PM

Thanks so much for your quick reply Richard. I have attached the worksheet with correct data up until April. It seems every 7 days it gets skewed for a week and then for the next 7 its ok. I have interchanged some of the numbers in the formula and was able to manually achieve what I have in the spreadsheet, kinda (for week one I needed to change the numbers in the formula sometimes, but week 2 the formula was removed from the cells completely and manually types in).

Thank you for your help in looking into this. If it cant be done, I can just manually create the table. I wanted to have it in a table so when I create a user input form to record the time the pay period can be selected from a drop down list and somehow or another all the dates in that period will show up on the user form for entry and then record each persons hours with the date and their name to a table. Then I will have a lookup table to calculate the pay times their rate in another table. THen on another worksheet or book, I will have the grand total of that pay period in dollars and I can do some calculations on the compilation sheet that will show year to date.

Ok so dont laugh, I dont even know if this is possible, I have been trying to ponder this whole project for literally the last 20 hours. Based on all the responses I got, the scenario is what I came up with, sketchy I know, still thinking through it and researching.

Thanks for your help! I have learned a lot today from everyone, it is awesome.

@Tilioso - (I don't see where I can attach my file, am I missing it?)

**tilioso** · 03-13-2012, 02:14 PM

I cannot find anywhere to upload the file even in private message so I hope I don't get in trouble for this but I have copied a good bit of data below. I can always email you the file if that would work.

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/16/12 - 01/29/12 Jan-12
1/23/2012 01/16/12 - 01/29/12 Jan-12
1/24/2012 01/16/12 - 01/29/12 Jan-12
1/25/2012 01/16/12 - 01/29/12 Jan-12
1/26/2012 01/16/12 - 01/29/12 Jan-12
1/27/2012 01/16/12 - 01/29/12 Jan-12
1/28/2012 01/16/12 - 01/29/12 Jan-12
1/29/2012 01/16/12 - 01/29/12 Jan-12
1/30/2012 01/30/12 - 02/12/12 Feb-12
1/31/2012 01/30/12 - 02/12/12 Feb-12
2/1/2012 01/30/12 - 02/12/12 Feb-12
2/2/2012 01/30/12 - 02/12/12 Feb-12
2/3/2012 01/30/12 - 02/12/12 Feb-12
2/4/2012 01/30/12 - 02/12/12 Feb-12
2/5/2012 01/30/12 - 02/12/12 Feb-12
2/6/2012 01/30/12 - 02/12/12 Feb-12
2/7/2012 01/30/12 - 02/12/12 Feb-12
2/8/2012 01/30/12 - 02/12/12 Feb-12
2/9/2012 01/30/12 - 02/12/12 Feb-12
2/10/2012 01/30/12 - 02/12/12 Feb-12
2/11/2012 01/30/12 - 02/12/12 Feb-12
2/12/2012 01/30/12 - 02/12/12 Feb-12
2/13/2012 02/13/12 - 02/26/12 Feb-12
2/14/2012 02/13/12 - 02/26/12 Feb-12
2/15/2012 02/13/12 - 02/26/12 Feb-12
2/16/2012 02/13/12 - 02/26/12 Feb-12
2/17/2012 02/13/12 - 02/26/12 Feb-12