How to calculate pay period between interval of 15 days

[Bala!aditya]

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.

[BillieJ]

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 formula 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
• compensated 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.

[JustineBB]

To calculate the elapsed time between two dates, we can subtract two dates and give us the days passed. It has to put the cell where the subtraction is effected GENERAL cell format. Excel uses a system to work with dates of assigning to each date a serial number starting with 1-Jan-1900. So by subtracting two dates gives the number of days between them. In Excel there is a formula, not documented in the help, not listed in the wizard of functions, called: = DATEDIF (start date, end date, type).