Pay dates between two dates

[daveasu]

I'm attempting to calculate the dates for payroll for contract labor. Our payroll is on the 1st and 15th of the month.

Contract dates are:
A1 has the Start Date: 6/23/2019
A2 has he End Date: 8/17/2019

I'm using this formula to calculate the number of pay periods:
=SUMPRODUCT(--(DAY(ROW(INDIRECT(A1&":"&A2)))={1,15}))

Which is returning the value of 4 (the number of pay periods between the dates)

However, since the end date is 2 days into the next pay period, there are actually 5 pay periods.

07/01/2019
07/15/2019
08/01/2019
08/15/2019
09/01/2019

Is there a formula that can calculate the 5 pay periods and return the pay date of each pay period based on the contract start and end dates?

 

[Marcelo Branco]

Maybe...

=SUMPRODUCT(--(DAY(ROW(INDIRECT(A1&":"&A2)))={1,15}))+IF(AND(DAY(A2)<>1,DAY(A2)<>15),1,0)

M.

 

[Repush]

if you would rather have the dates, try this UDF:

Function PayDays(FromDate As Date, ToDate As Date) As String
    For PDate = FromDate To ToDate
        If Day(PDate) = 1 Or Day(PDate) = 15 Then
            result = result & ", " & Format(PDate, "mm/dd/yyyy")
        End If
    Next PDate
    If Day(PDate) < 15 Then
        result = result & ", " & Format(DateSerial(Year(PDate), Month(PDate), 15), "mm/dd/yyyy")
    Else
        result = result & ", " & Format(DateSerial(Year(PDate), Month(PDate) + 1, 1), "mm/dd/yyyy")
    End If
    PayDays = Mid(result, 3, 999)
End Function

 

[daveasu]

Thank you Marcelo! That worked perfectly! Repush thank you! Yes, I would like to have the dates as well. It will take me a day or two to get my VBA hat on correctly and give this a try!

Thank you both!

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.