none
Report multiple pay periods with totals RRS feed

  • 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

    Monday, July 25, 2016 8:50 PM

Answers

  • 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

    Monday, July 25, 2016 11:03 PM

All replies

  • 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

    Monday, July 25, 2016 11:03 PM
  • Dear Ken,

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

    Thanks, 

    Barry

    Monday, July 25, 2016 11:52 PM