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?

Small OOPS in the previously posted routines. The UDF should read:

===========================
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(StartDate) = 1 Then FirstStartDate = StartDate

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

and the worksheet formula should be:

=SUMPRODUCT(--(DAY(ROW(INDIRECT(IF(DAY(StartDate)=1,
StartDate,IF(AND(DAY(StartDate)1,DAY(StartDate)<= 16),DATE(
YEAR(StartDate),MONTH(StartDate),16),StartDate - 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))))))={1,16}))*(IF(DAY(
StartDate)=1,StartDate,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(EndDate)<15,EndDate-DAY(
EndDate),DATE(YEAR(EndDate),MONTH(EndDate),15))))


--ron