Answered by:
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
Monday, July 25, 2016 8:50 PM
Answers

The following function returns the weekstarting 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, 17 (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 zerobased set of numbers for each twoweekly period. You can see this in the debug window if you enter some literal values:
StartDate = #20160703#
? (WeekStart(1,#20160706#)StartDate)\14
0
? (WeekStart(1,#20160712#)StartDate)\14
0
? (WeekStart(1,#20160721#)StartDate)\14
1
? (WeekStart(1,#20160725#)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
 Proposed as answer by Deepak Saradkumar PanchalMicrosoft contingent staff Tuesday, July 26, 2016 4:06 AM
 Marked as answer by Deepak Saradkumar PanchalMicrosoft contingent staff Monday, August 1, 2016 5:10 AM
Monday, July 25, 2016 11:03 PM
All replies

The following function returns the weekstarting 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, 17 (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 zerobased set of numbers for each twoweekly period. You can see this in the debug window if you enter some literal values:
StartDate = #20160703#
? (WeekStart(1,#20160706#)StartDate)\14
0
? (WeekStart(1,#20160712#)StartDate)\14
0
? (WeekStart(1,#20160721#)StartDate)\14
1
? (WeekStart(1,#20160725#)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
 Proposed as answer by Deepak Saradkumar PanchalMicrosoft contingent staff Tuesday, July 26, 2016 4:06 AM
 Marked as answer by Deepak Saradkumar PanchalMicrosoft contingent staff Monday, August 1, 2016 5:10 AM
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