Unanswered: Calculating days Mon to Friday excluding weekends
Dates seem to be my nemesis!
I have two feilds DateStart and DateEnd.
I have a query based on these [DateEnd]-[DateStart] which gives the total number of days between but I only want the number of working days (ie. Monday to Friday Inclusive) within the date ranges. I do not need to worry about other holidays.
Note: Within the query there is a field [FinDate] that converts Null values in the DateEnd to todays date.
I'm still at level beginner 101, maybe 104, and this looks looks like advanced 909 and while impressive I have no idea what to do with it.
I was hoping for some form of criteria or simple addition to the SQL code that would point to some inbuilt ability in Access..
Everything I can find identifies Mondays thru Fridays in single fields but not when they are asked to be identified with a date range.
I really appreciate the pointer but as I said I don't know what to do with it.
heh heh, no problem
I don't believe Access has anything "built-in" that will do what you're asking, but I'll see if I can at least help you out to accomplish it.
If you only need to worry about not counting weekends, then I would suggest looking here for a start (else if you need holidays, this site has a similar function to include them). It starts to explain what to do with the code, but it's assuming you know a thing or two already. No worries, here's what you do.
In the main access window, go the Public Modules section and create a new one. It may have some text in it already (like "Option Explicit" or something), but at the end of that paste the code from the above site. Save the module.
Now that you've told Access what this function is and what to do with it, it's time to use it. Go to your query, and do something like the following:
I have read through the Microsoft knowledge base article and have tried to add the sub at the bottom of the screen. But the bit I can't work out is how to select which 2 dates it creates the calculation between or how to link it to the [DateEnd] and [DateStart] in my tables?
Dim BegDate As Variant
Dim EndDate As Variant
Dim Work_Days As Variant
' Note that this function does not account for holidays.
Dim WholeWeeks As Variant
Dim DateCnt As Variant
Dim EndDays As Integer
On Error GoTo Err_Work_Days
BegDate = DateValue(BegDate)
EndDate = DateValue(EndDate)
WholeWeeks = DateDiff("w", BegDate, EndDate)
DateCnt = DateAdd("ww", WholeWeeks, BegDate)
EndDays = 0
Do While DateCnt <= EndDate
If Format(DateCnt, "ddd") <> "Sun" And _
Format(DateCnt, "ddd") <> "Sat" Then
EndDays = EndDays + 1
DateCnt = DateAdd("d", 1, DateCnt)
Work_Days = WholeWeeks * 5 + EndDays
' If either BegDate or EndDate is Null, return a zero
' to indicate that no workdays passed between the two dates. '
If Err.Number = 94 Then
Work_Days = 0
' If some other error occurs, provide a message.
MsgBox "Error " & Err.Number & ": " & Err.Description
You don't have to "link it to your tables" or anything of the sort. If you've created a public module and pasted the code into it, you've just told your application what this function Work_Dates does. By the way, you need to copy it exactly from the ms page into your module, I noticed you no longer have the arguments in the first line.
Once you have the function in place, then you can use it very easily via SQL by just calling out the function along with the two dates in question. If you post your SQL (and tell me which fields you're trying to calc' between) I'll see if I can help you out some more.