Workdays between two dates

[jeromez]

I have two dates:


StartDate = 11/09/2009


ReportDate = 11/30/2009



I know that days elapsed is simply:
[ReportDate] - [StartDate] = 21 Days


But how do I get just WORKDAYS?



The result would be 16 days in this example.

 

[RuralGuy]

Have you tried searching this site? There are may examples for you to use.

 

[jeromez]

Have you tried searching this site? There are may examples for you to use.

I've searched, but I can't make much sense of the results.

It looks like i have to have VBA code and record that as a function.

I'm not sure how I can incorporate that into my query so that the expression calculates the difference.

I need a little guidance =)

 

[RuralGuy]

You are correct in that creating a public function in a standard module if the solution most often suggested. Then your query simply creates a new field set to:
WorkDiff:=WorkDates([FirstField],[SecondField])

 

[jeromez]

You are correct in that creating a public function in a standard module if the solution most often suggested. Then your query simply creates a new field set to:
WorkDiff:=WorkDates([FirstField],[SecondField])

thank you RuralGuy!

I got it to work.

I've attached an example of the database I created to test the code given to my by Carl Dobbs (thanks Carl).

Taking that code I created a form (see "Form1") and also used that function in the query (see query "Qry_ElapsedWorkDays") (thanks RuralGuy for showing how it simply goes into the query field).

Again, thanx for you help and hopefully anyone searching on this in the future can simply download the database I attached!

Jerome

[FONT=Times New Roman][SIZE=3]Function Work_Days(BegDate As Variant, EndDate As Variant) As Integer[/SIZE][/FONT]
 
[SIZE=3][FONT=Times New Roman] Dim WholeWeeks As Variant[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman] Dim DateCnt As Variant[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman] Dim EndDays As Integer[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman] On Error GoTo Err_Work_Days[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman] BegDate = DateValue(BegDate)[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman] EndDate = DateValue(EndDate)[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman] WholeWeeks = DateDiff("w", BegDate, EndDate)[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman] DateCnt = DateAdd("ww", WholeWeeks, BegDate)[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman] EndDays = 0[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman] Do While DateCnt <= EndDate[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    If Format(DateCnt, "ddd") <> "Sun" And _[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]      Format(DateCnt, "ddd") <> "Sat" Then[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]       EndDays = EndDays + 1[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    End If[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    DateCnt = DateAdd("d", 1, DateCnt)[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman] Loop[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman] Work_Days = WholeWeeks * 5 + EndDays[/FONT][/SIZE]
 
[FONT=Times New Roman][SIZE=3]Exit Function[/SIZE][/FONT]
 
[SIZE=3][FONT=Times New Roman]      Err_Work_Days:[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman]      ' If either BegDate or EndDate is Null, return a zero[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]      ' to indicate that no workdays passed between the two dates.[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman]      If Err.Number = 94 Then[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]                              Work_Days = 0[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]      Exit Function[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]      Else[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3]' If some other error occurs, provide a message.[/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]      MsgBox "Error " & Err.Number & ": " & Err.Description[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]      End If[/FONT][/SIZE]
 
[FONT=Times New Roman][SIZE=3]End Function[/SIZE][/FONT]

 

[raskew]

Hi -

Give this a try:

Public Function fGetWorkdays2(pstart As Date, pend As Date) As Integer
'************************************************
'Purpose:   Compute number of workdays (Mon - Fri)
'           between pStart and pEnd
're:        http://www.access-programmers.co.uk/forums/showthread.php?t=164254&highlight=simplified
'Coded by:  raskew
'Input:    ? fGetWorkdays2(#2/10/06#, #7/13/06#)
'Output:    110
'************************************************

   fGetWorkdays2 = 7 - WeekDay(pstart) + 5 * (DateDiff("ww", pstart, pend) - 1) + WeekDay(pend) - 1

End Function

HTH - Bob