Hi,

In a 401(k) plan, employees enter the plan on the first date of the next payroll period after they've been employed for 120 days. I can calculate 120 days after their date of hire. But 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 any date. For example:

Let's say the employer's first 2016 bi-weekly payroll period runs from Sunday 1/3/2016 to Monday 1/16/2016, the second runs from 1/17/2016 to 1/30/2016, and so on. An employee has been working 120 days for the employer on 8/10/2016. Does anyone have a formula that will return the first date of the next bi-weekly pay period after 8/10/2016 (8/14/2016)?

Thanks in advance of your response!

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

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

Hi Garry, I just edited my question a bit, hopefully that clarifies what I was needing, I do apologize for any confusion! Thanks for your response.

Hi Garry, I just edited my question a bit, hopefully that clarifies
what
I was needing, I do apologize for any confusion! Thanks for your
response.

I don't see your revision! Where did you post it?

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

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?

Thanks in advance of your response!

Have a look here for *PatPeriodTables.xls*...

https://app.box.com/s/23yqum8auvzx17h04u4f

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

Typo...
Have a look here for *PayPeriodTables.xls*...

https://app.box.com/s/23yqum8auvzx17h04u4f

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