Typo...

You are saying that between Jan1 and Jan12 nobody got paid!

Also, 8/10/2016 is a Wednesday; -is this a PayDate? If so then the
1st PayDate in 2016 was Jan13 for PayPeriod workdays Dec13-Dec26 of

2015. That logic places today in week2 of PayPeriod19; its pay period
[i]
workdays are Aug21-Sep3; its PayDate is Sep21.

Because payroll PayDates fall in calendar years, what you should be
doing is entering the 1st PayDate for a calendar year and then calc
backwards to get when the *PayPeriod workdays* for that 1st pay of
2016 start/end. Now you can correctly calc forward for all PayPeriods
that follow.

If I understand correctly you're in luck! I'm currently in the
process of converting my WeeklyTimesheet.xlt into an addin. It
automatically loads timesheets for employees based on login
credentials, and performs other admin/payroll functions when all
timesheets are completed for their respective pay period. Each week
of a biweekly pay period is flagged 'A' or 'B' so they can be
processed by Payroll on a weekly basis.(Typical of most payroll
systems)

I wrote VBA procedures to calc pay periods based on week number in
the current calendar year. For example, today (Aug31) falls in
PayPeriod19B as shown he

Aug29-Sep3 (pp19B) PayDate: Wed, Sep 21, 2016

and PayPeriod19A is Aug21-Aug28.

If your PayDates follow your pay period work days biweekly then work
for PayPeriod1 in 2016 actually started Sun Dec 13 2015, ended Sat
Dec 26 2015, and Pay1 was Wed Jan 13 2016. PayPeriod2 started Sun Dec
27 2015 and ended Sat Jan 9 2016, and Pay2 was Wed Jan 27 2016. And
so on depending on your 1st PayDay in 2016 (Dec28). Using my table
structure, if your biweekly payday is a Wed...

Across columns A to K starting with Row1:

1 [A]Year [b]PayPeriod [C]StartDate [D]EndDate [E]PayDate [F]PayNdx
[G]13 [H]5 < PayNdx2 [J]PayFrq [K]14

2 [A]2016 [b]1 [C]=EndDate-PayNdx [D]=PayDate-(PayNdx+PayNdx2)
[E]Jan 13, 2016

3 [A] [b]=LastCell+1 [C]=EndDate-PayNdx [D]=PayDate-(PayNdx+PayNdx2)
[E]=LastCell+PayFrq

Copy B3:E3 down until you get the last PayDate for this year.

At this point you could continue for the years to follow. I put the
year in colA of the first row of each year as shown, and set 50%
taller RowHeight for spacing between years. I also Group years from
PayPeriods 2 to 26 with summary rows above.

The above formulas will generate the '#NAME?' error until you create
the defined names they use...

Select A3, open the Defined Name dialog, then create the following
names with local (sheet level) scope...

Name: StartDate RefersTo: =$C3

Name: EndDate RefersTo: =$D3

Name: PayDate RefersTo: =$E3

Name: LastCell RefersTo: =A2 (fully relative)

Name: PayNdx RefersTo: =$G$1 (weekdays offset)

Name: PayNdx2 RefersTo: =$H$1 (payday forward offset)

Name: PayFrq RefersTo: =$J$1 (payday frequency)


The 1st 3 names are col-absolute, row-relative; the last 2 are fully
absolute. If using early version Excel, to force local scope you
prepend the names with the sheetname as follows...

Name: BiWeekly!ThisYr

OR - if the sheetname has spaces...

Name: 'Pay Periods'!ThisYr

Now you can generate biweekly pay period tables simply by typing the
1st paydate of a calendar year into E2, then the weekdays offset in
G1, then enter its payday forward offset from Friday in H1.

Weekly pay periods PayNdx is 6, PayFrq is 7. You could have this calc
in another sheet by copying BiWeekly, rename it "Weekly", and extend
the table to 52 pay periods.

HTH...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus