473,395 Members | 1,440 Online

# Datediff not including weekday?????

I am trying to get the date difference between two dates but I don't
want the function to include weekends in the calculation. Does anyone
have an idea on how to make this work?

Jun 6 '06 #1
6 7626
ke**********@gmail.com wrote:
I am trying to get the date difference between two dates but I don't
want the function to include weekends in the calculation. Does anyone
have an idea on how to make this work?

Here is what I use to count weekend days:

'---Begin Code
Public Function CountWeekendDays(dtStart As Date, dtEnd As Date) As
Integer
Dim intSat As Integer
Dim intSun As Integer

'This function assumes dtStart <= dtEnd
CountWeekendDays = 0
intSat = DateDiff("d", GEDay(dtStart, 7), LEDay(dtEnd, 7)) / 7 + 1
intSun = DateDiff("d", GEDay(dtStart, 1), LEDay(dtEnd, 1)) / 7 + 1
CountWeekendDays = Ramp(intSat) + Ramp(intSun)
End Function

Public Function LEDay(dtX As Date, vbDay As Integer) As Date
LEDay = DateAdd("d", -(7 + WeekDay(dtX) - vbDay) Mod 7, dtX)
End Function

Public Function GEDay(dtX As Date, vbDay As Integer) As Date
GEDay = DateAdd("d", (7 + vbDay - WeekDay(dtX)) Mod 7, dtX)
End Function

Public Function Ramp(varX As Variant) As Variant
Ramp = IIf(Nz(varX, 0) >= 0, Nz(varX, 0), 0)
End Function
'---End Code

Sample Call:
MsgBox (CountWeekendDays(#3/1/06#, #5/1/06#))

18

Use the same dates you are using for your DateDiff in this function and
subtract the result from your DateDiff result.

James A. Fortune
CD********@FortuneJames.com

Jun 6 '06 #2
I am sure that works well in forms or macros, but will it work as an
expression in a query?

I should have noted, I am trying to do this through a query.

Sorry

CD********@FortuneJames.com wrote:
ke**********@gmail.com wrote:
I am trying to get the date difference between two dates but I don't
want the function to include weekends in the calculation. Does anyone
have an idea on how to make this work?

Here is what I use to count weekend days:

'---Begin Code
Public Function CountWeekendDays(dtStart As Date, dtEnd As Date) As
Integer
Dim intSat As Integer
Dim intSun As Integer

'This function assumes dtStart <= dtEnd
CountWeekendDays = 0
intSat = DateDiff("d", GEDay(dtStart, 7), LEDay(dtEnd, 7)) / 7 + 1
intSun = DateDiff("d", GEDay(dtStart, 1), LEDay(dtEnd, 1)) / 7 + 1
CountWeekendDays = Ramp(intSat) + Ramp(intSun)
End Function

Public Function LEDay(dtX As Date, vbDay As Integer) As Date
LEDay = DateAdd("d", -(7 + WeekDay(dtX) - vbDay) Mod 7, dtX)
End Function

Public Function GEDay(dtX As Date, vbDay As Integer) As Date
GEDay = DateAdd("d", (7 + vbDay - WeekDay(dtX)) Mod 7, dtX)
End Function

Public Function Ramp(varX As Variant) As Variant
Ramp = IIf(Nz(varX, 0) >= 0, Nz(varX, 0), 0)
End Function
'---End Code

Sample Call:
MsgBox (CountWeekendDays(#3/1/06#, #5/1/06#))

18

Use the same dates you are using for your DateDiff in this function and
subtract the result from your DateDiff result.

James A. Fortune
CD********@FortuneJames.com

Jun 6 '06 #3
I found a solution that looks like it works in query functions

DateDiff('d',[test]![date1],[test]![date2],2)-(IIf(DateDiff('ww',[test]![date1],[test]![date2],2)=0,DateDiff('ww',[test]![date1],[test]![date2],2),(DateDiff('ww',[test]![date1],[test]![date2],2))*2))

ke**********@gmail.com wrote:
I am sure that works well in forms or macros, but will it work as an
expression in a query?

I should have noted, I am trying to do this through a query.

Sorry

CD********@FortuneJames.com wrote:
ke**********@gmail.com wrote:
I am trying to get the date difference between two dates but I don't
want the function to include weekends in the calculation. Does anyone
have an idea on how to make this work?

Here is what I use to count weekend days:

'---Begin Code
Public Function CountWeekendDays(dtStart As Date, dtEnd As Date) As
Integer
Dim intSat As Integer
Dim intSun As Integer

'This function assumes dtStart <= dtEnd
CountWeekendDays = 0
intSat = DateDiff("d", GEDay(dtStart, 7), LEDay(dtEnd, 7)) / 7 + 1
intSun = DateDiff("d", GEDay(dtStart, 1), LEDay(dtEnd, 1)) / 7 + 1
CountWeekendDays = Ramp(intSat) + Ramp(intSun)
End Function

Public Function LEDay(dtX As Date, vbDay As Integer) As Date
LEDay = DateAdd("d", -(7 + WeekDay(dtX) - vbDay) Mod 7, dtX)
End Function

Public Function GEDay(dtX As Date, vbDay As Integer) As Date
GEDay = DateAdd("d", (7 + vbDay - WeekDay(dtX)) Mod 7, dtX)
End Function

Public Function Ramp(varX As Variant) As Variant
Ramp = IIf(Nz(varX, 0) >= 0, Nz(varX, 0), 0)
End Function
'---End Code

Sample Call:
MsgBox (CountWeekendDays(#3/1/06#, #5/1/06#))

18

Use the same dates you are using for your DateDiff in this function and
subtract the result from your DateDiff result.

James A. Fortune
CD********@FortuneJames.com

Jun 6 '06 #4
Similar to others, and does work in query

workdays
=DateDiff("d",[StartDate],[EndDate])-(DateDiff("ww",[StartDate],[EndDate],7)+DateDiff("ww",[StartDate],[EndDate],1))
+ 1

Jun 6 '06 #5
Ron2006 wrote:
Similar to others, and does work in query

workdays
=DateDiff("d",[StartDate],[EndDate])-(DateDiff("ww",[StartDate],[EndDate],7)+DateDiff("ww",[StartDate],[EndDate],1))
+ 1

I have read here that using ww for some versions of Access doesn't
return the correct value for some dates. That's why I didn't use it.
Maybe someone can clarify which situations may be dangerous..

James A. Fortune
CD********@FortuneJames.com

Jun 8 '06 #6
ke**********@gmail.com wrote:
I am sure that works well in forms or macros, but will it work as an
expression in a query?

I should have noted, I am trying to do this through a query.

Sorry

If the code is placed in a module rather than behind a form then you
can call it from a query.

James A. Fortune
CD********@FortuneJames.com

Jun 8 '06 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

### Similar topics

 1 by: Tim::. | last post by: I am having some difficulties with the function Datediff! I am trying to calculate the number of days between two given date E.G: Number days between 03-05-2004 and 05-05-2004 = I am using the... 4 by: Paolo | last post by: I am having some problem with a Year Function. I have form on which I have 4 field which indicate dates and an additional form which sums those dates: These are the fields: YEARS... 5 by: mcbill20 | last post by: Hello all. I have a really basic question that I hope someone has a better answer for. I apologize in advance-- I know this is probably a really basic question but I am used to Oracle rathern than... 3 by: haydn_llewellyn | last post by: Hi, My company runs on a fiscal calendar that starts on the first monday in July, and is based on a 13 week quarter (4 weeks, 4 weeks, 5 weeks). What I need, is a way of relating Date() to the... 1 by: Kd | last post by: I am currently using a form with Weekdays Mon Tues Wed Thur Fri This is generated by a table that the days are being entered manually I would like to create a form that the days updated... 3 by: Price Brattin | last post by: Why is the DateDiff function in the following code returning zero? Dim FileDate, TransmissionDate as Date Dim TranDay, FileDay, DayDiff as Inteter TransmissionDate = #2/5/2006 1:57:56 PM#... 4 by: dgmoore | last post by: I've hit a snag - I know this is easy, but the logic is escaping me. I need to set criteria in a query to find dates in a date field that lie between Tuesday of the current week and Tuesday of the... 0 by: Rudi Hausmann | last post by: Hi! I have a column with a date. In another column I want to show the weekday of this date. I have following code: ". The problem is that using the GNU compilers,... 0 by: Hystou | last post by: Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.