|
| |
|
|
CraigBFG
Posts: 211
Joined: 7/25/2003
From: UK
Status: offline
 |
Number of working days between 2 dates - 9/30/2003 7:54:06
Can anyone help me with a script to calculate the number of working days between 2 dates from a db?
Thanks
CraigBFG
|
|
|
|
simonw
Posts: 11
Joined: 9/30/2003
Status: offline
|
RE: Number of working days between 2 dates - 9/30/2003 10:44:42
Hi
Check out article 210532 at the Microsoft Knowledgebase
http://support.microsoft.com/default.aspx
Function Work_Days(BegDate As Variant, EndDate As Variant) As Integer
' Note that this function does not account for holidays.
Dim WholeWeeks As Variant
Dim DateCnt As Variant
Dim EndDays As Integer
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
End If
DateCnt = DateAdd("d", 1, DateCnt)
Loop
Work_Days = WholeWeeks * 5 + EndDays
End Function
Regards
Simon
_____________________________
Microsoft Access Product Specialist
| |
|
|
|
CraigBFG
Posts: 211
Joined: 7/25/2003
From: UK
Status: offline
|
RE: Number of working days between 2 dates - 11/12/2003 10:44:36
Had to change script function to run on ASP but now keep getting format error on line :-
If Format(DateCnt, "ddd") <> "Sun" And _.
Any ideas?
<%
Function Work_Days(BegDate,EndDate)
' Note that this function does not account for holidays.
Dim WholeWeeks
Dim DateCnt
Dim EndDays
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
End If
DateCnt = DateAdd("d", 1, DateCnt)
Loop
Work_Days = WholeWeeks * 5 + EndDays
End Function
%>
| |
|
|
|
Long Island Lune
Posts: 2340
Joined: 6/8/2002
From: New York
Status: offline
|
RE: Number of working days between 2 dates - 11/12/2003 11:31:19
quote:
If Format(DateCnt, "ddd") <> "Sun" And _
Format(DateCnt, "ddd") <> "Sat" Then
What's the error?
Why the _ ?
If Format(DateCnt, "ddd") <> "Sun" And Format(DateCnt, "ddd") <> "Sat" Then
< Message edited by Long Island Lune -- 11/12/2003 2:25:42 PM >
_____________________________
| |
|
|
|
CraigBFG
Posts: 211
Joined: 7/25/2003
From: UK
Status: offline
|
RE: Number of working days between 2 dates - 11/12/2003 11:34:28
Dunno, didn't write it. But the error is.
Microsoft VBScript runtime error '800a000d'
Type mismatch: 'Format'
| |
|
|
|
CraigBFG
Posts: 211
Joined: 7/25/2003
From: UK
Status: offline
|
RE: Number of working days between 2 dates - 11/12/2003 11:35:39
The function was written by MS but is to run from an Access db, where as I want to run the function from an ASP page.
| |
|
|
|
CraigBFG
Posts: 211
Joined: 7/25/2003
From: UK
Status: offline
|
Cracked it!! - 11/12/2003 12:36:52
Made a few tweaks, and heres the function that works a treat!
<%
Function Work_Days(BegDate,EndDate)
' Note that this function does not account for holidays.
Dim WholeWeeks
Dim DateCnt
Dim EndDays
BegDate = DateValue(BegDate)
EndDate = DateValue(EndDate)
WholeWeeks = DateDiff("w", BegDate, EndDate)
DateCnt = DateAdd("ww", WholeWeeks, BegDate)
EndDays = 0
Do While DateCnt <= EndDate
If WeekDay(BegDate) <> vbSunday AND WeekDay(EndDate) <> vbSaturday Then
' If WeekDay(BegDate) <> vbSunday AND WeekDay(EndDate) <> vbSaturday Then
EndDays = EndDays + 1
End If
DateCnt = DateAdd("d", 2, DateCnt)
Loop
Work_Days = WholeWeeks * 5 + EndDays
End Function
%>
| |
|
|
|
CraigBFG
Posts: 211
Joined: 7/25/2003
From: UK
Status: offline
|
Spoke too soon - 11/13/2003 4:47:05
The function works great outside of a query, ie calling
<% Response.Write Work_Days((FP_Field(fp_rs,"Timestamp")),(FP_Field(fp_rs,"DateClosed")))%>
However, if I try to incorporate the function within a DRW query, i get "Undefined function Work_Days", even though it clearly is.
SELECT tblJobs.UId, tblUsers.OrgName, Count(tblJobs.DefId) AS CountOfDefId, tblDefCatType.DCTId, tblDefCatType.DefCatType, tblDefStd.DefStdDesc, Work_Days([tbljobs.DateAssigned],[tblJobs.DateConfirmed]) AS AvgResp etc etc etc
I'm guessing this is something to do with the fact that the DRW is calling the db directly, not using the ASP function. IS the resolution to this to include the function within the db itself?
Can my goal be acheived???
| |
|
|
|
Long Island Lune
Posts: 2340
Joined: 6/8/2002
From: New York
Status: offline
|
RE: Spoke too soon - 11/13/2003 11:28:16
The DRW does not recognize your function. There are certian functions that SQL defines itself. And you can use many Access and VB functions too. But I'm not sure about custom functions.
What I would do is call your Work_Days function prior to the DRW. Assign the results of your Work_Days function to a variable, then use that variable inside your DRW. This should solve the problem.
_____________________________
| |
|
|
|
CraigBFG
Posts: 211
Joined: 7/25/2003
From: UK
Status: offline
|
RE: Spoke too soon - 11/13/2003 11:53:35
Thanks
The problem here is that the results are obtained within the DRW. Currently the query gets the average number of days between 2 dates(Using DateDiff,d), but what I need it to show me is the average number of Working Days - hence the function.
Unless anyone knows of a working days function that can be used within a drw!?!
| |
|
|
|
clum1
Posts: 758
From: Glasgow, Scotland
Status: offline
|
RE: Spoke too soon - 6/4/2004 8:57:40
I've just found this post, popped the function in my page and it works great; thanks guys.
I use it with dates returned from an SQL search, using the format:
<%=Work_Days(RSAbsence("datStartDate"), RSAbsence("datEndDate"))%>
to work out the number of work days between the start of someone's absence and the end. Does this help?
calum
_____________________________
kenilweb.com; simple, effective web design
"So I said to the Gym instructor "Can you teach me to do the splits?" He said "How flexible are you?". I said "I can't make Tuesdays"."
Tim Vine
| |
|
|
|
clum1
Posts: 758
From: Glasgow, Scotland
Status: offline
|
RE: Spoke too soon - 6/7/2004 6:24:13
It's not a UK issue - it just doesn't seem to work correctly! Anyone any ideas at all?
clum1
< Message edited by clum1 -- 6/7/2004 6:26:10 >
_____________________________
kenilweb.com; simple, effective web design
"So I said to the Gym instructor "Can you teach me to do the splits?" He said "How flexible are you?". I said "I can't make Tuesdays"."
Tim Vine
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
Cheers! 
