# Pay dates between two dates

#### daveasu

##### New Member
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?

Last edited:

### Excel Facts

To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

#### Marcelo Branco

##### MrExcel MVP
Maybe...

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

M.

#### Repush

##### Board Regular
if you would rather have the dates, try this UDF:
Code:
``````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

##### New Member
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

##### MrExcel MVP
You are welcome. Thanks for the feedback.

M.

Replies
18
Views
210
Replies
19
Views
203
Replies
2
Views
141
Replies
7
Views
81
Replies
7
Views
67