Number of semi-monthly periods between 2 dates

[sforr]

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?

 

[bj]

assuming that if the start date in the 15th only the second period would be
used and if the 15th was the last day no periods for that month would be
counted.
if there are two cells with Start-date and End-Date
try
= (year(End-Date)-Year(Start-date))*24+(month(Start-date)-month(End
Date))*2+if(day(start-date)<16,0,-1)+if(day(end-date)>15,0,-1)

 

[Biff]

Hi!

Try this.

A1 = start date
B1 = end date

Requires the Analysis ToolPak add-in be installed.

=SUMPRODUCT(--(DAY(ROW(INDIRECT(A1&":"&B1)))=15))*2+OR(B1=EOMONTH(B1,0),DAY(B1)<15)-(DAY(A1)<=15)

This version does not require the ATP:

=SUMPRODUCT(--(DAY(ROW(INDIRECT(A1&":"&B1)))=15))*2+OR(B1=DATE(YEAR(B1),MONTH(B1)+1,0),DAY(B1)<15)-(DAY(A1)<=15)

Biff

 

[Ron Rosenfeld]

Hi!

Try this.

A1 = start date
B1 = end date

Requires the Analysis ToolPak add-in be installed.

=SUMPRODUCT(--(DAY(ROW(INDIRECT(A1&":"&B1)))=15))*2+OR(B1=EOMONTH(B1,0),DAY(B1)<15)-(DAY(A1)<=15)

You are not counting ONLY full pay periods.

For example:

StartDate = 1/13/2005
End Date = 3/18/2005

Your formula(s) --> 5

Full Periods:

1/16 - 1/31
2/1 - 2/15
2/16 - 2/28
3/1 - 3/15


--ron

 

[Ron Rosenfeld]

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(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(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))))

HTH,

--ron

 

[Ron Rosenfeld]

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(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))))


--ron

 

[Ron Rosenfeld]

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?

One other small modification: Remove the two lines in the UDF that begin with
Debug.Print

I also cleaned up some potential line wrap issues on this copy.

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

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

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


--ron

 

[Biff]

You are not counting ONLY full pay periods.

That's correct, I was only counting pay dates.

Biff

 

[Harlan Grove]

Biff wrote...

Try this.

A1 = start date
B1 = end date ....
This version does not require the ATP:

=SUMPRODUCT(--(DAY(ROW(INDIRECT(A1&":"&B1)))=15))*2
+OR(B1=DATE(YEAR(B1),MONTH(B1)+1,0),DAY(B1)<15)-(DAY(A1)<=15)

....

Why not brute force with a slight twist?

=SUMPRODUCT(--(DAY(ROW(INDIRECT(A1&":"&B1))+{0,1})={15,1}))

 

[Harlan Grove]

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(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(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))))

Ugh!

If one can live with an array formula, why not

=INT((MAX(IF(DAY(ROW(INDIRECT(StartDate&":"&EndDate))+1)={1,16},
ROW(INDIRECT(StartDate&":"&EndDate))))
-MIN(IF(DAY(ROW(INDIRECT(StartDate&":"&EndDate)))={1,16},
ROW(INDIRECT(StartDate&":"&EndDate)))))/15)

?

 

[Ron Rosenfeld]

Ron Rosenfeld wrote...
...

Ugh!

If one can live with an array formula, why not

=INT((MAX(IF(DAY(ROW(INDIRECT(StartDate&":"&EndDate))+1)={1,16},
ROW(INDIRECT(StartDate&":"&EndDate))))
-MIN(IF(DAY(ROW(INDIRECT(StartDate&":"&EndDate)))={1,16},
ROW(INDIRECT(StartDate&":"&EndDate)))))/15)

?

Oh I agree with "Ugh!" and, as you know, I don't mind array formulas. Although
I'm happy with my UDF.

What I did was translate my UDF algorithm into worksheet code. Shorter
worksheet code would be better. But yours gives an incorrect result for, among
other examples:

StartDate: 3 Jan 2005
EndDate: 15 Mar 2005

Your formula gives a result of '3'. I believe correct answer is '4'.

16 Jan -- 31 Jan
1 Feb -- 15 Feb
16 Feb -- 28 Feb
1 Mar -- 15 Mar

By the way, the code in the message of mine you quoted also gives incorrect
results in some instances (although not this instance); and was corrected later
in the thread.

Best,
--ron

 

[Biff]

Using Ron's sample date range:

StartDate = 1/13/2005
End Date = 3/18/2005

Formula returns 3.

Biff

 

[Harlan Grove]

Ron Rosenfeld wrote...

What I did was translate my UDF algorithm into worksheet code. Shorter
worksheet code would be better. But yours gives an incorrect result for, among
other examples:

StartDate: 3 Jan 2005
EndDate: 15 Mar 2005

Your formula gives a result of '3'. I believe correct answer is '4'.

....

You're right. I didn't consider short periods at the beginning of the
year, in which February would screw up dividing days by 15 to get half
month counts.

An opportunity to simplify the formula. Now not even an array formula.

=SUMPRODUCT((DAY(ROW(INDIRECT(StartDate&":"&EndDate)))={1,16,16,16,16})
*(DAY(ROW(INDIRECT(StartDate&":"&EndDate))+{0,13,14,15,16})=1)
*(ROW(INDIRECT(StartDate&":"&EndDate))+{14,12,13,14,15}<=EndDate))

 

[Ron Rosenfeld]

An opportunity to simplify the formula. Now not even an array formula.

=SUMPRODUCT((DAY(ROW(INDIRECT(StartDate&":"&EndDate)))={1,16,16,16,16})
*(DAY(ROW(INDIRECT(StartDate&":"&EndDate))+{0,13,14,15,16})=1)
*(ROW(INDIRECT(StartDate&":"&EndDate))+{14,12,13,14,15}<=EndDate))

Very nice!


--ron

 

[Biff]

Isn't it amazing how some threads deal with "complicated" solutions and
generate some really top notch contributions yet the OP is nowhere in sight!

Biff

 

[Ron Rosenfeld]

Isn't it amazing how some threads deal with "complicated" solutions and
generate some really top notch contributions yet the OP is nowhere in sight!

Yes it is. But an important reason for my participation here is to advance my
own state of knowledge; so these threads are still valuable to me.


--ron