473,322 Members | 1,620 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

How to find out the number of Mondays in a month?

sueb
379 256MB
I need to count the number of each weekday in a month (# of Mondays, # of Tuesdays, etc.). Is this even possible in Access?

Let me see if I can explain the reason I need these 5 counts.

In our Operating Room, the time is allocated to different classes of surgeries (General, Neurology, Pediatrics, etc.), and we want to compare the actual minutes spent in each category with what was allocated, and the allocations are made per day-of-the-week.

Complicating the issue is that different amounts are allocated on different weekdays; for instance, General surgeries are allocated 450 minutes a day, except that it's allocated only 420 minutes on Mondays. Ob-Gyn is allocated 450 minutes on Tuesdays and Wednesdays, and Plastics is allocated 240 minutes on Tuesdays and Fridays, and 450 minutes on Thursdays. You get the idea.

So I can calculate the correct "monthly" allocation only if I can figure out how many of each weekday occurs in a given month.

Any advice will be appreciated, even including "can't be done in Access". However, if that's the answer, I'll be most grateful for tips about how to accomplish this "outside" in such a way that I can use the results within Access.

Thanks!
Mar 21 '11 #1

✓ answered by NeoPa

A routine to determine the number of particular days of the week in a given month might go something like this :
Expand|Select|Wrap|Line Numbers
  1. Public Function NumWeekdays(ByVal datThis As Date, _
  2.                             Optional ByVal intDay As Integer = vbMonday)
  3.     datThis = DateAdd("d", 1 - Day(datThis), datThis)
  4.     intDay = (intDay Mod 7) + 1
  5.     intDay = Weekday(datThis, intDay) - 1
  6.     NumWeekdays = DateDiff("d", datThis, DateAdd("m", 1, datThis))
  7.     NumWeekdays = (NumWeekdays + intDay) \ 7
  8. End Function
It's not too easy to explain as it gets a bit fiddly, but if you look through it you might pick it up.

11 11440
Rabbit
12,516 Expert Mod 8TB
First you calculate how many days it takes to get to the next Monday. Then you subtract that from the amount of days in the month. If that's less than 29, there's 4 Mondays in the month, otherwise it's 5 Mondays. The same goes for the other days of the week.
Mar 21 '11 #2
sueb
379 256MB
OOh, I like that! But let me make sure I understand how this is working: Wouldn't that "29" have to change depending on how many days there are in the month? I looked for a VBA function that would give me that number, but couldn't find it. Is there one? Or do I have to set up a table that just contains the number of days in each month (also thinking about leap years)?
Mar 21 '11 #3
NeoPa
32,556 Expert Mod 16PB
A routine to determine the number of particular days of the week in a given month might go something like this :
Expand|Select|Wrap|Line Numbers
  1. Public Function NumWeekdays(ByVal datThis As Date, _
  2.                             Optional ByVal intDay As Integer = vbMonday)
  3.     datThis = DateAdd("d", 1 - Day(datThis), datThis)
  4.     intDay = (intDay Mod 7) + 1
  5.     intDay = Weekday(datThis, intDay) - 1
  6.     NumWeekdays = DateDiff("d", datThis, DateAdd("m", 1, datThis))
  7.     NumWeekdays = (NumWeekdays + intDay) \ 7
  8. End Function
It's not too easy to explain as it gets a bit fiddly, but if you look through it you might pick it up.
Mar 21 '11 #4
sueb
379 256MB
@Rabbit, so what I'm going to do, I think, is calculate the number of days in the month by subtracting 1 day from the first day of the next month and extracting the 'day' from that calculated date.

However, it turns out that I don't know how to construct a date. I want to have a code something like:

Expand|Select|Wrap|Line Numbers
  1. Public Function Count_Weekdays(Month_Start As Date)
  2. On Error GoTo Err_Count_Weekdays
  3.  
  4.     Dim Next_Month
  5.     Dim Next_Month_s_Year
  6.     Dim Days_This_Month As Integer
  7.     Dim Days_To_Next As Integer
  8.     Dim Monday_Count As Integer
  9.  
  10.     ' Get number of days in the month
  11.     Next_Month_s_Year = DatePart(yyyy, Month_Start)
  12.     If DatePart(m, Month_Start) = 12 Then
  13.         Next_Month_s_Year = Next_Month_s_Year + 1
  14.     End If
  15.     'Next_Month = (1, datepart(m,Month_Start)+1, Next_Month_s_Year)
  16.     'Days_This_Month = day(Next_Month-1)
  17.  
  18.     ' For each weekday:
  19.     '   Calculate number of Days_To_Next Monday
  20.     '   if Days_This_Month - Days_To_Next < Days_This_Month - 1 then
  21.     '       Monday_Count = 4
  22.  
  23.     ' Calculate minutes allocation for each Surgery type for this month
  24.     '(make an update query to update a table holding these values?)
  25.  

but I don't really know how to write line 15. (and I'll worry about what follows after I get at least this part right!)
Mar 21 '11 #5
sueb
379 256MB
Oh, NeoPa, I might have known you'd just have this in your back pocket!
Mar 21 '11 #6
gershwyn
122 100+
This is a function I use all the time that will calculate the number of days in a given month:
Expand|Select|Wrap|Line Numbers
  1. Public Function DaysInMonth(Month, Year)
  2.   DaysInMonth = Day(DateSerial(Year, Month + 1, 0))
  3. End Function
  4.  
The dateSerial part is a little tricky, but I'll explain. The last day of the current month is equivalent to the day before the first day of next month. DateSerial(Year, Month+1, 0) will give us that day, and the day function extracts just the day portion (you could also use DatePart for the same thing, but I think this is cleaner.)

You could do something similar, using the Weekday (or DatePart) functions to figure out what day of week the first day of the month was, and that should give you the other piece you need to calculate the number of days using Rabbit's method.

Edit: I see the Bytes ninjas are in full force today. You anticipated my function before I posted it and NeoPa has a full answer already.
Mar 21 '11 #7
sueb
379 256MB
Wow, Gershwyn, this little function is a real eye-opener for me--I didn't know the DateSerial function at all. Does this get the right year when Month is December? (I mean I assume it does, or you wouldn't use it "all the time"...)
Mar 21 '11 #8
gershwyn
122 100+
Yes, it is smart enough to wrap around to the next year if needed. So if you specify a month of 12, it will calculate (Month + 1) = 13 and proceed with January, the same way it knows to treat a day of 0 as the previous day. It also handles leap years wonderfully, without having to code for the exceptions.

Test case:
Expand|Select|Wrap|Line Numbers
  1. ?DaysInMonth(1, 11)
  2.  31 
  3.  
  4. ?DaysInMonth(2, 11)
  5.  28 
  6.  
  7. ?DaysInMonth(12, 11)
  8.  31 
  9.  
  10. ?DaysInMonth(2, 12)
  11.  29 
  12.  
Mar 21 '11 #9
NeoPa
32,556 Expert Mod 16PB
It certainly does Sue. See the simple test below (run in the Immediate pane of the debugger).

Expand|Select|Wrap|Line Numbers
  1. ?CDate(DateSerial(2011,13,0))
  2. 31/12/2011
Mar 21 '11 #10
sueb
379 256MB
@Gershwyn: that's lovely--very elegant. Okay, I'll try out these options and post back here which one I end up using.
Mar 21 '11 #11
sueb
379 256MB
Okay, I'm going with NeoPa's code for now (although I saved Gershwyn's off because I think I'll need it for another project later).

I now have more questions about how to accomplish my final goal of comparing "actual" with "allocated" minutes, but I'll ask those in other threads.

Thanks, everyone!
Mar 22 '11 #12

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: josepe | last post by:
Hello, I need know the week nuber of the month. I know the function datepart() but returns the week number of the year a number between (1,53) I need a number between (1,4 o5). There is a...
7
by: developer | last post by:
I want to substract a number of month from a specific date. someone have a easy solution ? Thanks
5
by: Ali Baba | last post by:
Hi, Is there is equivalent of VB's DateDiff() method in C#. I need to find difference in months between two dates that are years apart. Docs says that I can use TimeSpan like: TimeSpam ts =...
10
by: Bill | last post by:
Hello -- I'm parsing the output of the finger command, and was wondering something...If I'm given a month abbrievation (such as "Jan"), what's the best way to figure out the month number? I see...
0
Vasuki Masilamani
by: Vasuki Masilamani | last post by:
Hi, Could anyone please help me with a query to get the number of working days in a month. Thanks in advance. Vasuki.
2
by: CindySue | last post by:
Hello--I've got a query that uses DatePart("m",) to select records for a particular month, and then a report based on that query. I'd like to be able to create a field in the report that says what...
3
by: sunmat | last post by:
To find number of character without space using java Example: String = prabu sun No. of char =8 plz send me code
5
by: girl23 | last post by:
I am using a function prototype to convert month number to month name. basically you ask the compiler to enter int from 1 to 12 and then convert it. printMonth ( number ); is the prototype and...
12
by: Be Borth | last post by:
I saw previous solutions to convert a month number (1) to a month name (January). I have a database with 200+ dates. In a query, I use the "Part" function DatePart("m",), to extract the month...
3
by: Praveen Raj V | last post by:
"socket.Available" is not working. How would you Determine number of bytes present in a TCP/IP socket before reading those bytes without using socket.Available void Receive(Socket socket, byte...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.