Luke said:JOM,
I've struggled with this before. Weekends (and holidays) always need to
be excluded when I do date calculations.
My basic solution is to add a public function in a module to do the
work... then, it's a matter of accumulating weekdays between the two
dates. As an example (Access 2000):
Public Function BizDayDiff(lhs as Date, rhs as Date) As Integer
' lhs = Left Hand Side and rhs = Right Hand Side
' as in "lhs - rhs"
' Left Hand Side should always be the greater date
' you can test and swap if you want to.
BizDayDiff = 0
Dim bookmark As Date:
bookmark = lhs
Dim w As Integer:
w = 0
Do While (bookmark < rhs)
' increment the counter
bookmark = DateAdd("d", 1, bookmark)
' increment the count on non-weekends ("W" returns day of week 1-7)
w = DatePart("W", bookmark)
If w <> 1 And w <> 7 Then BizDayDiff = BizDayDiff + 1
Loop
End Function
I think this should work ok. Keep in mind that, the loop runs n times
where n is the actual number of days between two dates. This means that
it scales pretty horribly if your distance between dates is large in
general.
Just set your query to this function with the two dates you want - diff:
BizDayDiff([date1], [date2]) where date1 needs to be larger than date 2
or it will output 0.
There may be a more efficient way to do this, but this should work
Good luck,
Luke
I have a query that am trying to subtract 2 dates excluding weekeds
who do I do that in a query... I have datediff('d',date,datecompleted)
Please help!
Luke Dalessandro said:LOL,
With all of my lhs, rhs nonsense (comes from C++ operator overloading
habits) I got them backwards...
Assign bookmark = rhs (the lower date) and test bookmark < lhs (the
larger date) in the loop.
Sorry,
Luke
Luke said:JOM,
I've struggled with this before. Weekends (and holidays) always need to
be excluded when I do date calculations.
My basic solution is to add a public function in a module to do the
work... then, it's a matter of accumulating weekdays between the two
dates. As an example (Access 2000):
Public Function BizDayDiff(lhs as Date, rhs as Date) As Integer
' lhs = Left Hand Side and rhs = Right Hand Side
' as in "lhs - rhs"
' Left Hand Side should always be the greater date
' you can test and swap if you want to.
BizDayDiff = 0
Dim bookmark As Date:
bookmark = lhs
Dim w As Integer:
w = 0
Do While (bookmark < rhs)
' increment the counter
bookmark = DateAdd("d", 1, bookmark)
' increment the count on non-weekends ("W" returns day of week 1-7)
w = DatePart("W", bookmark)
If w <> 1 And w <> 7 Then BizDayDiff = BizDayDiff + 1
Loop
End Function
I think this should work ok. Keep in mind that, the loop runs n times
where n is the actual number of days between two dates. This means that
it scales pretty horribly if your distance between dates is large in
general.
Just set your query to this function with the two dates you want - diff:
BizDayDiff([date1], [date2]) where date1 needs to be larger than date 2
or it will output 0.
There may be a more efficient way to do this, but this should work
Good luck,
Luke
I have a query that am trying to subtract 2 dates excluding weekeds
who do I do that in a query... I have datediff('d',date,datecompleted)
Please help!
I am getting an error with the first line ie the
Public Function BizDayDiff(lhs as Date, rhs as Date) As Integer
what should I do?
:
LOL,
With all of my lhs, rhs nonsense (comes from C++ operator overloading
habits) I got them backwards...
Assign bookmark = rhs (the lower date) and test bookmark < lhs (the
larger date) in the loop.
Sorry,
Luke
Luke said:JOM,
I've struggled with this before. Weekends (and holidays) always need to
be excluded when I do date calculations.
My basic solution is to add a public function in a module to do the
work... then, it's a matter of accumulating weekdays between the two
dates. As an example (Access 2000):
Public Function BizDayDiff(lhs as Date, rhs as Date) As Integer
' lhs = Left Hand Side and rhs = Right Hand Side
' as in "lhs - rhs"
' Left Hand Side should always be the greater date
' you can test and swap if you want to.
BizDayDiff = 0
Dim bookmark As Date:
bookmark = lhs
Dim w As Integer:
w = 0
Do While (bookmark < rhs)
' increment the counter
bookmark = DateAdd("d", 1, bookmark)
' increment the count on non-weekends ("W" returns day of week 1-7)
w = DatePart("W", bookmark)
If w <> 1 And w <> 7 Then BizDayDiff = BizDayDiff + 1
Loop
End Function
I think this should work ok. Keep in mind that, the loop runs n times
where n is the actual number of days between two dates. This means that
it scales pretty horribly if your distance between dates is large in
general.
Just set your query to this function with the two dates you want - diff:
BizDayDiff([date1], [date2]) where date1 needs to be larger than date 2
or it will output 0.
There may be a more efficient way to do this, but this should work
Good luck,
Luke
JOM wrote:
I have a query that am trying to subtract 2 dates excluding weekeds
who do I do that in a query... I have datediff('d',date,datecompleted)
Please help!
Luke Dalessandro said:Hmm,
Where are you putting the function/ what version of access are you using?
I write for Access 2000 (vb6). This would go in a public module.
What is the error specifically, my code might have a bug in it...
Luke
I am getting an error with the first line ie the
Public Function BizDayDiff(lhs as Date, rhs as Date) As Integer
what should I do?
:
LOL,
With all of my lhs, rhs nonsense (comes from C++ operator overloading
habits) I got them backwards...
Assign bookmark = rhs (the lower date) and test bookmark < lhs (the
larger date) in the loop.
Sorry,
Luke
Luke Dalessandro wrote:
JOM,
I've struggled with this before. Weekends (and holidays) always need to
be excluded when I do date calculations.
My basic solution is to add a public function in a module to do the
work... then, it's a matter of accumulating weekdays between the two
dates. As an example (Access 2000):
Public Function BizDayDiff(lhs as Date, rhs as Date) As Integer
' lhs = Left Hand Side and rhs = Right Hand Side
' as in "lhs - rhs"
' Left Hand Side should always be the greater date
' you can test and swap if you want to.
BizDayDiff = 0
Dim bookmark As Date:
bookmark = lhs
Dim w As Integer:
w = 0
Do While (bookmark < rhs)
' increment the counter
bookmark = DateAdd("d", 1, bookmark)
' increment the count on non-weekends ("W" returns day of week 1-7)
w = DatePart("W", bookmark)
If w <> 1 And w <> 7 Then BizDayDiff = BizDayDiff + 1
Loop
End Function
I think this should work ok. Keep in mind that, the loop runs n times
where n is the actual number of days between two dates. This means that
it scales pretty horribly if your distance between dates is large in
general.
Just set your query to this function with the two dates you want - diff:
BizDayDiff([date1], [date2]) where date1 needs to be larger than date 2
or it will output 0.
There may be a more efficient way to do this, but this should work
Good luck,
Luke
JOM wrote:
I have a query that am trying to subtract 2 dates excluding weekeds
who do I do that in a query... I have datediff('d',date,datecompleted)
Please help!
Ok I saw where I went wrong, I tried to change the names of your fields ihs
and rhs to date and datefollowup... but am getting an error at date...please
help!
:
Hmm,
Where are you putting the function/ what version of access are you using?
I write for Access 2000 (vb6). This would go in a public module.
What is the error specifically, my code might have a bug in it...
Luke
I am getting an error with the first line ie the
Public Function BizDayDiff(lhs as Date, rhs as Date) As Integer
what should I do?
:
LOL,
With all of my lhs, rhs nonsense (comes from C++ operator overloading
habits) I got them backwards...
Assign bookmark = rhs (the lower date) and test bookmark < lhs (the
larger date) in the loop.
Sorry,
Luke
Luke Dalessandro wrote:
JOM,
I've struggled with this before. Weekends (and holidays) always need to
be excluded when I do date calculations.
My basic solution is to add a public function in a module to do the
work... then, it's a matter of accumulating weekdays between the two
dates. As an example (Access 2000):
Public Function BizDayDiff(lhs as Date, rhs as Date) As Integer
' lhs = Left Hand Side and rhs = Right Hand Side
' as in "lhs - rhs"
' Left Hand Side should always be the greater date
' you can test and swap if you want to.
BizDayDiff = 0
Dim bookmark As Date:
bookmark = lhs
Dim w As Integer:
w = 0
Do While (bookmark < rhs)
' increment the counter
bookmark = DateAdd("d", 1, bookmark)
' increment the count on non-weekends ("W" returns day of week 1-7)
w = DatePart("W", bookmark)
If w <> 1 And w <> 7 Then BizDayDiff = BizDayDiff + 1
Loop
End Function
I think this should work ok. Keep in mind that, the loop runs n times
where n is the actual number of days between two dates. This means that
it scales pretty horribly if your distance between dates is large in
general.
Just set your query to this function with the two dates you want - diff:
BizDayDiff([date1], [date2]) where date1 needs to be larger than date 2
or it will output 0.
There may be a more efficient way to do this, but this should work
Good luck,
Luke
JOM wrote:
I have a query that am trying to subtract 2 dates excluding weekeds
who do I do that in a query... I have datediff('d',date,datecompleted)
Please help!
Luke Dalessandro said:JOM,
I'm not sure why Date would be causing a problem. I'm using Access 2000
with VB6. You may need to define them as "Datetime" instead of date.
Luke
Ok I saw where I went wrong, I tried to change the names of your fields ihs
and rhs to date and datefollowup... but am getting an error at date...please
help!
:
Hmm,
Where are you putting the function/ what version of access are you using?
I write for Access 2000 (vb6). This would go in a public module.
What is the error specifically, my code might have a bug in it...
Luke
JOM wrote:
I am getting an error with the first line ie the
Public Function BizDayDiff(lhs as Date, rhs as Date) As Integer
what should I do?
:
LOL,
With all of my lhs, rhs nonsense (comes from C++ operator overloading
habits) I got them backwards...
Assign bookmark = rhs (the lower date) and test bookmark < lhs (the
larger date) in the loop.
Sorry,
Luke
Luke Dalessandro wrote:
JOM,
I've struggled with this before. Weekends (and holidays) always need to
be excluded when I do date calculations.
My basic solution is to add a public function in a module to do the
work... then, it's a matter of accumulating weekdays between the two
dates. As an example (Access 2000):
Public Function BizDayDiff(lhs as Date, rhs as Date) As Integer
' lhs = Left Hand Side and rhs = Right Hand Side
' as in "lhs - rhs"
' Left Hand Side should always be the greater date
' you can test and swap if you want to.
BizDayDiff = 0
Dim bookmark As Date:
bookmark = lhs
Dim w As Integer:
w = 0
Do While (bookmark < rhs)
' increment the counter
bookmark = DateAdd("d", 1, bookmark)
' increment the count on non-weekends ("W" returns day of week 1-7)
w = DatePart("W", bookmark)
If w <> 1 And w <> 7 Then BizDayDiff = BizDayDiff + 1
Loop
End Function
I think this should work ok. Keep in mind that, the loop runs n times
where n is the actual number of days between two dates. This means that
it scales pretty horribly if your distance between dates is large in
general.
Just set your query to this function with the two dates you want - diff:
BizDayDiff([date1], [date2]) where date1 needs to be larger than date 2
or it will output 0.
There may be a more efficient way to do this, but this should work
Good luck,
Luke
JOM wrote:
I have a query that am trying to subtract 2 dates excluding weekeds
who do I do that in a query... I have datediff('d',date,datecompleted)
Please help!
Tim said:If you are looking for a solution to Count the days in between two dates i
would choose to implement this,
http://www.mvps.org/access/datetime/date0012.htm. It counts only business
workdays and is pretty easy to use like the datediff function. Instead i
believe its CountWorkdaysA(date1, date2, arrayofholidays). This will exclude
weekends and whatever dates are in the holidays.
Tim
Luke Dalessandro said:JOM,
I'm not sure why Date would be causing a problem. I'm using Access 2000
with VB6. You may need to define them as "Datetime" instead of date.
Luke
Ok I saw where I went wrong, I tried to change the names of your fields ihs
and rhs to date and datefollowup... but am getting an error at date...please
help!
:
Hmm,
Where are you putting the function/ what version of access are you using?
I write for Access 2000 (vb6). This would go in a public module.
What is the error specifically, my code might have a bug in it...
Luke
JOM wrote:
I am getting an error with the first line ie the
Public Function BizDayDiff(lhs as Date, rhs as Date) As Integer
what should I do?
:
LOL,
With all of my lhs, rhs nonsense (comes from C++ operator overloading
habits) I got them backwards...
Assign bookmark = rhs (the lower date) and test bookmark < lhs (the
larger date) in the loop.
Sorry,
Luke
Luke Dalessandro wrote:
JOM,
I've struggled with this before. Weekends (and holidays) always need to
be excluded when I do date calculations.
My basic solution is to add a public function in a module to do the
work... then, it's a matter of accumulating weekdays between the two
dates. As an example (Access 2000):
Public Function BizDayDiff(lhs as Date, rhs as Date) As Integer
' lhs = Left Hand Side and rhs = Right Hand Side
' as in "lhs - rhs"
' Left Hand Side should always be the greater date
' you can test and swap if you want to.
BizDayDiff = 0
Dim bookmark As Date:
bookmark = lhs
Dim w As Integer:
w = 0
Do While (bookmark < rhs)
' increment the counter
bookmark = DateAdd("d", 1, bookmark)
' increment the count on non-weekends ("W" returns day of week 1-7)
w = DatePart("W", bookmark)
If w <> 1 And w <> 7 Then BizDayDiff = BizDayDiff + 1
Loop
End Function
I think this should work ok. Keep in mind that, the loop runs n times
where n is the actual number of days between two dates. This means that
it scales pretty horribly if your distance between dates is large in
general.
Just set your query to this function with the two dates you want - diff:
BizDayDiff([date1], [date2]) where date1 needs to be larger than date 2
or it will output 0.
There may be a more efficient way to do this, but this should work
Good luck,
Luke
JOM wrote:
I have a query that am trying to subtract 2 dates excluding weekeds
who do I do that in a query... I have datediff('d',date,datecompleted)
Please help!