Date Calculation

[pgwhalen]

How can I calculate the number or working days between two records on a report. I know there is a NETWORKDAYS syntax but I must be formatting it incorrectly.

 

[DougP]

Have a look at this

This example uses the DateDiff function to display the number of days between a given date and today.

Dim TheDate As Date ' Declare variables.
Dim Msg
TheDate = InputBox("Enter a date&quot
Msg = "Days from today: " & DateDiff("d", Now, TheDate)
MsgBox Msg

DougP, MCP
dposton@universal1.com

Ask me how Bar-codes can help you be more productive.

 

[mike777]

Hey PGWhalen,
You're right, I think I remember seeing something about a "NetWorkDays" built-in function. But as I just searched for it and found nothing...I thought I'd throw together a little code to do the same thing. Hope this helps.
Sub mike()
Dim array_1() As Integer
Dim x As Integer, y As Integer, z As Integer
Dim date1 As Date, date2 As Date, date3 As Date
date1 = #12/1/2000#
date2 = Date
x = 0
If date2 >= date1 Then
Do Until date3 = date2
date3 = date1 + x
ReDim Preserve array_1(x + 1)
array_1(x + 1) = Weekday(date3)
' MsgBox Weekday(date3) & Chr(13) & date3 & Chr(13) & Format(date3, "dddd&quot
x = x + 1
Loop
' MsgBox UBound(array_1)
y = 1
z = 0
Do Until y = UBound(array_1)
' MsgBox array_1
If array_1 = 1 Or array_1 = 7 Then
z = z + 1
End If
y = y + 1
Loop
MsgBox "The number of net work days in this specified date period is " & _
UBound(array_1) - z
End If
End Sub
This counts the number of days between "date1" and "date2" and then subtracts out the Saturdays and Sundays. If you knew the dates of all the holidays, you could work those in there too and subtract those out also.
Since you're using this for a report, you'd want to make this a custom function and then just make the value of the field on the report the name of the function (pretty sure that's how that works).
I left some commented out message boxes in there in case you wanted to run the code with them in there to see how the thing works.
Hope this helps. Best of success to you.
Mike Kemp
kempmike65@aol.com

 

[mike777]

Oh yeah, this counts the first day in with it. So, in this example 12/1, Friday would be included as a workday.
If you want to exclude the first day, change line #7 to read x=1 (from x=0) and lines 11 and 12 as follows:

Line 11: Redim...array_1(x)
Line 12: array_1(x)=weekday...

from current:

Line 11: Redim...array_1(x+1)
Line 12: array_1(x+1)=weekday(blahblahblah)...

 

[MichaelRed]

[tab][tab][tab]Been There
[tab][tab][tab][tab]Done That


[tab][tab][tab][tab][tab]Go Here

http://www.tek-tips.com/faqs.cfm?spid=181&sfid=261&CFID=8828374&CFTOKEN=95286407

[tab][tab][tab][tab][tab][tab]Do This


MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[Quehay]

PgWhalen:

You know this is a real lacuna in the VB function offering. The ready-made offerings are clumsy and don't make provision for holidays (National and Company).

I've got a VBA code routine that will (1) give a date n[/n] workdays from a given date and (2)count the number of elapsed business days between two dates, excluding weekend days and also excluding any company holidays that you put into a Holiday Array.

If you throw it into a class module you can use it as two readily available functions.

It's a mutha with some borrowed code so, I'm not going to post it publicly without author sanction--send me an email and I'll send the code and an outline to you.

jjeito@aol.com

 

[Quehay]

Would you trust a code offering from someone who can't put an [/i] end tag in a posting?

 

[Quehay]

Update: The code I was referring to is posted on a web page, and it's attributed as a submission by Ken Getz (a renowned Access guru), so I guess it's OK to refer you to his routine publicly. Pardon the reference to another Access site (it offers excellent FAQ type resources but w/out discussion):

http://www.mvps.org/access

(see the Date/Time section).