Report multiple pay periods with totals

Question

I have a table with work dates, start time and end times.  I have a report entering start and end dates which works well.  I would like to be able to show multiple pay periods with totals for each 2 week period and grand totals at the bottom.  How can I get the information to be divided up at the startdate +14 and then start again at the next date + 14 etc.  Any information is greatly appreciated. 

Thanks,

Barry

Answer 1

The following function returns the week-starting date for any date:

Public Function WeekStart(intStartDay As Integer, Optional varDate As Variant)

    ' Returns 'week starting' date for any date
    
    ' Arguments:
    ' 1. intStartDay - weekday on which week starts, 1-7 (Sun - Sat)
    ' 2. varDate - optional date value for which week starting
    '   date to be returned.  Defaults to current date
    
    If IsMissing(varDate) Then varDate = VBA.Date
    
    If Not IsNull(varDate) Then
        WeekStart = DateValue(varDate) - Weekday(varDate, intStartDay) + 1
    End If
    
End Function

If you subtract the start date of the first week from the return value of the function, and then integer divide the result by 14 this will give you a zero-based set of numbers for each two-weekly period.  You can see this in the debug window if you enter some literal values:

StartDate = #2016-07-03#
? (WeekStart(1,#2016-07-06#)-StartDate)\14
 0
? (WeekStart(1,#2016-07-12#)-StartDate)\14
 0
? (WeekStart(1,#2016-07-21#)-StartDate)\14
 1
? (WeekStart(1,#2016-07-25#)-StartDate)\14
 1

If you include a computed column in the report's RecordSource query which uses the above expression you'll then be able to group the report on the computed column.

---

Ken Sheridan, Stafford, England

Answer 2

Dear Ken,

Thank you for the prompt reply.  I will investigate this solution and let you know if I have further questions.

Thanks, 

Barry