Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Number of semi-monthly periods between 2 dates
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? |
#2
|
|||
|
|||
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) "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? |
#3
|
|||
|
|||
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=EOMONT H(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(Y EAR(B1),MONTH(B1)+1,0),DAY(B1)<15)-(DAY(A1)<=15) Biff "sforr" wrote in message ... 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? |
#4
|
|||
|
|||
On Mon, 13 Jun 2005 23:03:50 -0400, "Biff" wrote:
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=EOMONT H(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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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? 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 |
#8
|
|||
|
|||
You are not counting ONLY full pay periods.
That's correct, I was only counting pay dates. Biff "Ron Rosenfeld" wrote in message ... On Mon, 13 Jun 2005 23:03:50 -0400, "Biff" wrote: 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=EOMONT H(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 |
#9
|
|||
|
|||
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})) |
#10
|
|||
|
|||
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(St artDate),16), StartDate-DAY(StartDate)+33-DAY(StartDate-DAY(StartDate)+32)) &":"&IF(MONTH(EndDate+1)<MONTH(EndDate),EndDat e, IF(DAY(EndDate)<15,EndDate-DAY(EndDate),DATE(YEAR(EndDate), MONTH(EndDate),15))))))={1,16}))*(IF(AND(DAY(Star tDate)1, DAY(StartDate)<=16),DATE(YEAR(StartDate),MONTH(St artDate),16), StartDate-DAY(StartDate)+33-DAY(StartDate-DAY(StartDate)+32)) <IF(MONTH(EndDate+1)<MONTH(EndDate),EndDate,IF(D AY(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) ? |
#11
|
|||
|
|||
On 14 Jun 2005 12:43:51 -0700, "Harlan Grove" wrote:
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&":"&EndDa te))+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 |
#12
|
|||
|
|||
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) ? |
#13
|
|||
|
|||
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&":"&EndDat e)))={1,16,16,16,16}) *(DAY(ROW(INDIRECT(StartDate&":"&EndDate))+{0,13,1 4,15,16})=1) *(ROW(INDIRECT(StartDate&":"&EndDate))+{14,12,13,1 4,15}<=EndDate)) |
#14
|
|||
|
|||
On 15 Jun 2005 11:46:12 -0700, "Harlan Grove" wrote:
An opportunity to simplify the formula. Now not even an array formula. =SUMPRODUCT((DAY(ROW(INDIRECT(StartDate&":"&EndDa te)))={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 |
#15
|
|||
|
|||
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" wrote in message ... On 15 Jun 2005 11:46:12 -0700, "Harlan Grove" wrote: An opportunity to simplify the formula. Now not even an array formula. =SUMPRODUCT((DAY(ROW(INDIRECT(StartDate&":"&EndD ate)))={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 |
#16
|
|||
|
|||
On Wed, 15 Jun 2005 22:11:55 -0400, "Biff" wrote:
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formating Dates for production schedule | Excel Discussion (Misc queries) | |||
Total number of each month in a column of dates | Excel Worksheet Functions | |||
Number format-numbers appear as dates in Excel | Excel Worksheet Functions | |||
Calculate the number of workdays between 2 dates | Excel Worksheet Functions | |||
Calculate the number of workdays between 2 dates | Excel Worksheet Functions |