Pay dates between two dates

daveasu

New Member
Joined
Jan 4, 2012
Messages
47
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:

Some videos you may like

Excel Facts

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

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,397
Maybe...

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

M.
 

Repush

Board Regular
Joined
Sep 21, 2015
Messages
133
Office Version
  1. 2013
Platform
  1. Windows
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
Joined
Jan 4, 2012
Messages
47
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,114,510
Messages
5,548,488
Members
410,840
Latest member
Kar3ousse
Top