473,395 Members | 1,440 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,395 software developers and data experts.

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: <asp:BoundField DataField="myDate" HeaderText="WeekDay"...
4
ddtpmyra
by: ddtpmyra | last post by:
I have below query inside the ms access, but this query counts all the days including the weekend but I only wanted to count the NUMBER of weekday (Monday to Friday). Please help Number of Days...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". 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.