Hi,
I am wondering if anyone knows a formula that will return the first
date of the next bi-weekly "Sunday to Saturday" payroll period after
a given date.
For example, the 2016 first bi-weekly payroll period runs from
1/3/2016 to 1/16/2016, the second runs from 1/17/2016 to 1/30/2016,
and so on. If I have a date in cell A1 of 8/10/2016, does anyone have
a formula that will return the first date of the next bi-weekly pay
period, 8/14/2016?
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 week1 of PayPeriod19; its pay period
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 [i]< 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