Number of semi-monthly periods between 2 dates

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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)
 
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
 
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)
Click to expand...

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
 
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?
Click to expand...

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
 
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?
Click to expand...

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
 
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?
Click to expand...

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
 
You are not counting ONLY full pay periods.

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

Biff
 
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)
Click to expand...
....

Why not brute force with a slight twist?

=SUMPRODUCT(--(DAY(ROW(INDIRECT(A1&":"&B1))+{0,1})={15,1}))
 
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))))
Click to expand...

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

?
Click to expand...

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
 
Using Ron's sample date range:

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

Formula returns 3.

Biff
 
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'.
Click to expand...
....

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))
 
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))
Click to expand...

Very nice!


--ron
 
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
 
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!
Click to expand...

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
 
Back
Top