Need formula help please.

[Seti]

Ok, I have a field that needs to calculate the dollar amount entered
(I12) divided by the number of pay periods left in the year. The number
of pay periods would have to be determined by the date eligible. The
date eligible would be 30 days following the hire date (F12) For
instance, someone is hired on 8/18/06 his eligible date would be
9/18/06, the next payroll date is 9/29/06 which is week 20 and leaves 7
pay periods left for the year. If they elect 2,000.00 for the year then
the deduction per pay period would be $285.71. The other problem I run
into is that every year the payroll weeks will change. So next year
week 20 will be 9/28/07.

 

[Richard Buttrey]

One way.

Note these dates are UK style so swap the day/month

First enter a range of period end dates. It appears from your posting
that your weeks end on a Friday and your year end is 11/5/2007 - week
52. This gives week 20 as 20/9/2006 per your posting.
Hence this list starts with 26/5/2006 and goes on with 30/6/2006,
28/7/2006 etc. each month ending with the last Friday of the month.
Extend it for a couple of years to give you some breathing room
Name this range "PeriodEnd"

Enter your year end 11/5/2007 in another cell and name this "YearEnd"

Now
I12 is 2000
J12 is 18/8/2006 - Hire Date
K12 is =J12+30 - Eligible Date
L12 is =INDEX(PeriodEnd,MATCH(K12-1,PeriodEnd,1)+1,1)
Next Payroll Date
and name L12 "NextPay"

M12 is =12+(MONTH(yearend)-MONTH(NextPay))-1
Periods left
N12 is =I12/M12


HTH

Ok, I have a field that needs to calculate the dollar amount entered
(I12) divided by the number of pay periods left in the year. The number
of pay periods would have to be determined by the date eligible. The
date eligible would be 30 days following the hire date (F12) For
instance, someone is hired on 8/18/06 his eligible date would be
9/18/06, the next payroll date is 9/29/06 which is week 20 and leaves 7
pay periods left for the year. If they elect 2,000.00 for the year then
the deduction per pay period would be $285.71. The other problem I run
into is that every year the payroll weeks will change. So next year
week 20 will be 9/28/07.

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________