Subtracting weekends

  • Thread starter Thread starter Shelby
  • Start date Start date
S

Shelby

I'm running a query to find the difference between two
dates. With the fields Date/time Administrator as my
start time and Date/time Analyst as my end time, I'm using
the following to calculate the number of hours elapsed
between the two times:
HoursElapsed: ([Date/time Analyst]-[Date/time
Administrator])*24-DateDiff("d",[Date/time Administrator],
[Date/time Analyst])*15

Can someone suggest how to modify this so that weekends
will be excluded as well?
 
Check the mvps.org site. They have an entire section on biz days.


--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
 
and what about holidays?

Here is a start:
DateDiff("w",[Date/time Administrator], [Date/time Analyst]) * 5 ' weeks *
working days
you will still have to account for overflow days as this will only give you
complete weeks

You might be better off creating a function that does this calculation for
you

HS
 
i would, but i'm not that advanced. thanks for the advice!
You might be better off creating a function that does this calculation for
you

HS



Shelby said:
I'm running a query to find the difference between two
dates. With the fields Date/time Administrator as my
start time and Date/time Analyst as my end time, I'm using
the following to calculate the number of hours elapsed
between the two times:
HoursElapsed: ([Date/time Analyst]-[Date/time
Administrator])*24-DateDiff("d",[Date/time Administrator],
[Date/time Analyst])*15

Can someone suggest how to modify this so that weekends
will be excluded as well?
Click to expand...


.
Click to expand...
 
Ok, so here is the code - put it in a code module
unction WorkingDays(StartDate As Date, EndDate As Date) As Integer
Dim iWeeks As Integer
Dim iDays As Integer

'First ensure taht the dates are different
If StartDate >= EndDate Then
WorkingDays = 0
Exit Function
Else

'Count the number of Whole weeks elapsed -
' a week is determined as 7 days from startdate regardless of day
iWeeks = DateDiff("w", StartDate, EndDate)

'Count days in fractional part of last week

If DatePart("w", EndDate) > DatePart("w", StartDate) Then
If DatePart("w", EndDate) >= vbFriday Then ' Adjust for saturday
iDays = vbFriday - DatePart("w", StartDate) + 1
Else
iDays = DatePart("w", EndDate) - DatePart("w", StartDate) +
1
End If
Else ' it must be wraparound week
iDays = 4 + DatePart("w", StartDate) - DatePart("w", EndDate)
End If

WorkingDays = iWeeks * 5 + iDays
End If
End Function


Shelby said:
i would, but i'm not that advanced. thanks for the advice!
You might be better off creating a function that does this calculation for
you

HS



Shelby said:
I'm running a query to find the difference between two
dates. With the fields Date/time Administrator as my
start time and Date/time Analyst as my end time, I'm using
the following to calculate the number of hours elapsed
between the two times:
HoursElapsed: ([Date/time Analyst]-[Date/time
Administrator])*24-DateDiff("d",[Date/time Administrator],
[Date/time Analyst])*15

Can someone suggest how to modify this so that weekends
will be excluded as well?
Click to expand...


.
Click to expand...
Click to expand...
 
Ouch, i wish I had thought of that...


Van T. Dinh said:
See The Access Web for the technique:

http://www.mvps.org/access/datetime/date0006.htm

--
HTH
Van T. Dinh
MVP (Access)



Shelby said:
I'm running a query to find the difference between two
dates. With the fields Date/time Administrator as my
start time and Date/time Analyst as my end time, I'm using
the following to calculate the number of hours elapsed
between the two times:
HoursElapsed: ([Date/time Analyst]-[Date/time
Administrator])*24-DateDiff("d",[Date/time Administrator],
[Date/time Analyst])*15

Can someone suggest how to modify this so that weekends
will be excluded as well?
Click to expand...
Click to expand...
 
Back
Top