I want my report to automatically calculate number of workdays in a month based off drop down choices (1 Viewer)

[Db-why-not]

I have some dropdown buttons where you can select a month and year and then it opens up a report for that month and year. In the report I want it to automatically calculate the number of workdays (weekdays) in that month that was selected.

I know in excel there is the EOMonth formula you can use. I'm not sure how to do this in access.

I'm not sure where to begin with this. Should i create a query that takes the month and year from the dropdown button, then create another field in my query that calculates the number of workdays. Or do I need to save the data that is selected from the dropdown boxes into a table?

I'm not sure what the formula would be to calculate the number of workdays automatically in each month either.

 

[plog]

EOMonth() in excel just generates the last date in a month, not the number of workdays. The first thing you need to do is define "workdays". Is it just every Monday, Tuesday, Wednesday, Thursday & Friday? Or does it also exclude holidays that fall on those days?

How many workdays are in this month? 22 or 23?

 

[Db-why-not]

EOMonth() in excel just generates the last date in a month, not the number of workdays. The first thing you need to do is define "workdays". Is it just every Monday, Tuesday, Wednesday, Thursday & Friday? Or does it also exclude holidays that fall on those days?

How many workdays are in this month? 22 or 23?

Workdays would include holidays. Mon -Friday would be workdays. December has 23 workdays.

 

[Gasman]

You could try searching for "Calculate workdays" here on this site?

 

[plog]

I would build a custom function in a module to calculate work days. You would pass it either a date or the year and month. It would then determine the total number of days in the month and subtract the number of Saturdays and Sundays in there.

This is going to require you use built-in Date functions listed here:

MS Access: Functions - Listed by Category

For easy reference, we have provided a list of all Microsoft Access functions sorted into categories such as string, numeric/mathematical, date/time, advanced/logical, domain aggregate, data type conversion, group by, and file/directory functions.

www.techonthenet.com

As Gasman has said, you should search this forum because this is a problem that's been addressd many times. You might be able to find a thread with code to accomplish exactly what you want.

 

[Gasman]

I would build a custom function in a module to calculate work days. You would pass it either a date or the year and month. It would then determine the total number of days in the month and subtract the number of Saturdays and Sundays in there.

This is going to require you use built-in Date functions listed here:

MS Access: Functions - Listed by Category

For easy reference, we have provided a list of all Microsoft Access functions sorted into categories such as string, numeric/mathematical, date/time, advanced/logical, domain aggregate, data type conversion, group by, and file/directory functions.

www.techonthenet.com

As Gasman has said, you should search this forum because this is a problem that's been addressd many times. You might be able to find a thread with code to accomplish exactly what you want.

And here is one such thread
https://www.access-programmers.co.uk/forums/threads/access-help.307938/#post-1735601

 

[CJ_London]

this is a small function I've used in the past for calculating the number of weekdays in any month specified by a date

Function weekdaysInMonth(ByVal dt As Date) As Integer
Dim i As Long

    For i = dt - Day(dt) + 1 To DateAdd("m", 1, dt) - Day(dt) 'first day of month to last day of month

        weekdaysInMonth = weekdaysInMonth - (Weekday(i, vbMonday) < 6)

    Next i

End Function

 

[arnelgp]

same function:

Public Function numWorkDays(ByVal Month As Integer, ByVal Year As Long) As Integer
    Dim i As Integer
    Dim last As Integer
    Dim total_days As Integer
    last = Day(DateSerial(Year, Month + 1, 0))
    total_days = last
    For i = 1 To last
        total_days = total_days + (InStr("sat/sun", Format$(DateSerial(Year, Month, i), "ddd")) > 0)
    Next
    numWorkDays = total_days
End Function