View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.misc
joel joel is offline
external usenet poster
 
Posts: 9,101
Default Calculate pay periods between two dates if paid on 1st and 15th?

I'm breaking the solution up into three formulas that need to be added so you
can understand the solution. You can combine the 3 forulas into 1 after you
verify the formulas are correct.


I'm assuming if the start date is the 15th or the end data is the 15th these
are pay dates and will be included in the answer. If not the formula in B3
need a minor adjustment.

A1 = 4/10/08
A2 = 5/20/08

Put in column B the following

B1 - the number of years * 2 pay periods per month). Partial years will be
comensated using the formula in B2.
=24*(YEAR(A2)-YEAR(A1))

B2 - The number of months * 2. Can be negative if Start month is after end
month. This wil compensate for a partial year.
=2*(MONTH(A2)-MONTH(A1)-1)

B3 - the pay periods in a partial month
=LOOKUP(DAY(A1),{1,2,16;2,1,0})+LOOKUP(A2,{1,15;1, 2})

The answer is the sum of these 3 formulas

"janplan" wrote:

How do I calculate the number of pay periods between two dates, if a person
is paid on the 1st and 15th of every month?