On Mon, 13 Jun 2005 11:42:03 -0700, "sforr"
wrote:

I need to calculate the number of semi-monthly pay periods between 2 dates,
with the pay periods being the 15th of the month and the last day of the
month. I only want complete periods. Any ideas?

The issue, of course, is that you only want COMPLETE periods between the two
dates.

It's relatively easy to devise a UDF (user defined function) in VBA.

To enter this, <alt-F11 opens the VB editor. Ensure your project is
highlighted inthe project explorer window, then Insert/Module and paste the
code below into the window that opens.

To use the UDF, in some cell enter the formula:

=semimonthly(StartDate,EndDate)

where StartDate and EndDate refer to the cells where you have that information.

==================================
Function SemiMonthly(StartDate As Date, EndDate As Date) As Long
Dim FirstStartDate As Date
Dim LastEndDate As Date
Dim i As Long

If Day(StartDate) 1 And Day(StartDate) <= 16 Then
FirstStartDate = DateSerial(Year(StartDate), Month(StartDate), 16)
Else
FirstStartDate = StartDate - Day(StartDate) + 33 - Day(StartDate -
Day(StartDate) + 32)
End If

If Day(EndDate) < 15 Then LastEndDate = EndDate - Day(EndDate)
If Day(EndDate) = 15 Then LastEndDate = DateSerial(Year(EndDate),
Month(EndDate), 15)
If Month(EndDate + 1) < Month(EndDate) Then LastEndDate = EndDate

Debug.Print StartDate & " " & Format(FirstStartDate, "mm-dd-yyyy")
Debug.Print EndDate & " " & Format(LastEndDate, "mm-dd-yyyy")

For i = FirstStartDate To LastEndDate
If Day(i) = 1 Or Day(i) = 16 Then
SemiMonthly = SemiMonthly + 1
End If
Next i

End Function
==============================

If you want a worksheet formula approach, the function below mimics the UDF and
should give the same result.

=SUMPRODUCT(--(DAY(ROW(INDIRECT(IF(AND(DAY(StartDate)1,DAY(
StartDate)<=16),DATE(YEAR(StartDate),MONTH(StartDa te),16),StartDate -
DAY(StartDate) + 33 - DAY(StartDate - DAY(StartDate) + 32))&":"&IF(
MONTH(EndDate+1)<MONTH(EndDate),EndDate,IF(DAY(En dDate)
<15,EndDate-DAY(EndDate),DATE(YEAR(EndDate),MONTH(
EndDate),15))))))={1,16}))*(IF(AND(DAY(StartDate) 1,DAY(StartDate)
<=16),DATE(YEAR(StartDate),MONTH(StartDate),16),St artDate -
DAY(StartDate) + 33 - DAY(StartDate - DAY(StartDate) + 32))<IF(
MONTH(EndDate+1)<MONTH(EndDate),EndDate,IF(DAY(
EndDate)<15,EndDate-DAY(EndDate),DATE(YEAR(EndDate),
MONTH(EndDate),15))))

HTH,

--ron