Using Ron's sample date range:
StartDate = 1/13/2005
End Date = 3/18/2005
Formula returns 3.
Biff
"Harlan Grove" wrote in message
oups.com...
Ron Rosenfeld wrote...
...
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(S tartDate),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(AND(DAY(Sta rtDate)1,
DAY(StartDate)<=16),DATE(YEAR(StartDate),MONTH(S tartDate),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) )))
Ugh!
If one can live with an array formula, why not
=INT((MAX(IF(DAY(ROW(INDIRECT(StartDate&":"&EndDat e))+1)={1,16},
ROW(INDIRECT(StartDate&":"&EndDate))))
-MIN(IF(DAY(ROW(INDIRECT(StartDate&":"&EndDate)))={ 1,16},
ROW(INDIRECT(StartDate&":"&EndDate)))))/15)
?
|