Individual weekday count in a month

[Silver_A]

Hi, I need help getting the count of individual weekdays for a month in a querry. I need to get the count for Mondays, Tuesdays, etc, then I will multiply this by the number of hours people work per day then added up to get their monthly work hours.

If anyone can please tell me how I can do do a individual weekday count for a month without using vba, your help will be greatly appreciated.

Thanks.

[Datagopherdan]

Theres a few different ways to get the day of the week in a query. Below are 2 ways you can use.

TheDayofTheWeek: Weekday([TheDate])
TheDayofTheWeek: Format([TheDate],"ddd")

The first one will give you numbers for the day. 1 is Sunday, 2 Monday etc.

The second one will give you the actual name of the day.

Once you get these values, you can create a crosstab query to get the counts for each day of the week.

Dan
Access Development

Hi, I need help getting the count of individual weekdays for a month in a querry. I need to get the count for Mondays, Tuesdays, etc, then I will multiply this by the number of hours people work per day then added up to get their monthly work hours.

If anyone can please tell me how I can do do a individual weekday count for a month without using vba, your help will be greatly appreciated.

Thanks.

[Silver_A]

Thank you so much for your reply Dan, actually what I need is a formula that give the number of mondays for a given month for example in January there were 5 mondays and 4 tuesday and so forth. I don't have individual dates, I just have a month.

Thanks you so much for any help you can give me.

[TheShabz]

I don't see how this can be done without VBA. you need some sort of counter to keep track of days in a week, 1-7, in order to count day types, and a counter for the number of months in a year 1-12, and assign each month a given number of days. This is something I had done in my intro to C++ class way back when.

Any reason why you dont want this in VBA?

[maximus]

This is a old thread were Sir Paul has given a link to a function to calculate the weekdays in a month see if this is any help.

https://www.accessforums.net/queries...ends-5142.html

[maximus]

I have wirtten this Code in vba. Save this as a module in your database and then you can use it to calculate the number of a specific day in between dates.

Syntax how to use it on forms, reports, query criteria

=NumberOfDays(StartingDate,EndingDate,DayIndexNumb er)

Day Index Numbers are:
Sunday=1
Monday=2
Tuesday=3
Wednesday=4
Thursday=5
Friday=6
Saturday=7

The Function as follows:

Option Compare Database
Dim dtStartDate As Date
Dim dtEndDate As Date
Dim intDayID As Integer
Dim intDifference As Integer
Dim intMonday As Integer
Dim intTuesDay As Integer
Dim intWednesday As Integer
Dim intThursday As Integer
Dim intFriday As Integer
Dim intSaturday As Integer
Dim intSunday As Integer
Dim intCounter As Integer
Dim intDayIndexNumber As Integer

'************************************************* *********
'************************************************* *********
'Written by Siddthartha Majumdar on 04/16/2010
'************************************************* *********
'Scope of the function: can calculate the occurence of a particular Day in a month
'Returns an integer value


Function NumberOfDays(dtStartDate, dtEndDate, intDayID) As Integer

If IsNull(dtStartDate) Then 'Exit the function if both the starting or the ending date is Null
Exit Function
ElseIf IsNull(dtEndDate) Then
Exit Function

Else

intDifference = DateDiff("d", dtStartDate, dtEndDate) 'Calculates the difference between two Dates in No of days

intMonday = 0
intTuesDay = 0
intWednesday = 0 'This variables stores the count of the days in integer value
intThursday = 0
intFriday = 0
intSaturday = 0
intSunday = 0


For intCounter = 0 To intDifference 'For loop checks each day

intDayIndexNumber = DatePart("w", DateAdd("d", intCounter, dtStartDate))
Select Case intDayIndexNumber
Case Is = 1
intSunday = intSunday + 1
Case Is = 2 'Select case adds 1 when the loop encounters a specific day
intMonday = intMonday + 1
Case Is = 3
intTuesDay = intTuesDay + 1
Case Is = 4
intWednesday = intWednesday + 1
Case Is = 5
intThursday = intThursday + 1
Case Is = 6
intFriday = intFriday + 1
Case Is = 7
intSaturday = intSaturday + 1
End Select
Next

Select Case intDayID
Case Is = 1 'Value returned according to the specification of the user
NumberOfDays = intSunday
Case Is = 2
NumberOfDays = intMonday
Case Is = 3
NumberOfDays = intTuesDay
Case Is = 4
NumberOfDays = intWednesday
Case Is = 5
NumberOfDays = intThursday
Case Is = 6
NumberOfDays = intFriday
Case Is = 7
NumberOfDays = intFriday
End Select
End If
End Function


I know you don't want to use VBA this is incase you change your mind.

[Datagopherdan]

The code provided is an excellent suggestion. There is also another route that you can take, which would not require writing alot of VBA.

You would create a table that had a listing of dates and the day of the week. The easiest way to do this is to make it in Excel and import it, so you can auto-fill.

I have used this method in the past over writing functions because many times, companies also want to be able to account for holidays and there are times where they might want a Saturday or Sunday counted. When you set it up in a table like this, you can designate days that are not to be counted such as holidays. This also makes it very flexible because the user will be able to designate days that are holidays and days that they want counted and not counted.

Dan
Access Development

Thank you so much for your reply Dan, actually what I need is a formula that give the number of mondays for a given month for example in January there were 5 mondays and 4 tuesday and so forth. I don't have individual dates, I just have a month.

Thanks you so much for any help you can give me.