What criteria would I use to group data based on last working day

Question

I've been scratching my brain over this for a while now.

Pretty amateur coder here using Microsoft Access mostly and have been attempting to make a query for some data which will work out a bonus for staff based on the amount of work they have completed.

I had worked it out to filter the data through criteria based on grouping by month([completiondate]) and multiplying the amount by a value for the types of cases.

However I have been advised that the work month for payroll runs from the last working day of the month to the day before the last working day of the following month.

Is there a way I can create this criteria even in a longer piece of data over the course of a year for example.

I will update this later with more info when I get onto my laptop just don't have it to hand and need to ask before I forget to.

Answer 1

You can use this function, passing a date of the current month and one of the next month:

' Returns the last workday of the month of Date1.
' Optionally, if WorkOnHolidays is True, holidays are regarded as workdays.
'
' Requires table Holiday with list of holidays.
'
' 2022-07-09. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function DateWorkdayMonthLast( _
    ByVal Date1 As Date, _
    Optional ByVal WorkOnHolidays As Boolean) _
    As Date
    
    Dim ResultDate  As Date

    ResultDate = DatePreviousWorkday(DateNextMonthPrimo(Date1), WorkOnHolidays)
    
    DateWorkdayMonthLast = ResultDate

End Function

It is an example of how to create a special function using generic functions.

These can be found in my library at GitHub: VBA.Date. (Too much code to post here).