this is the bain of my life at the moment and its driving me nuts. I found a formula to do it in xl but i cant get it in access. Its for a helpdesk project im working on so i need to know how many working hours a job has been open for. Any help would be really appreciated
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
how do i work out the number of working hours between 2 dates+times
- Thread starter jamiec1
- Start date
Maybe this will help...
Dim dtDate1 As Variant
Dim dtDate2 As Variant
Dim dtDiff As Variant
dtDiff = DateDiff("h", dtDate1, dtDate2)
This will give you the number of hours between two date/time values. Do some validation on the two original values and you should be able to figure out what you need. If you need more help let me know and I will do what I can!
HTH
Ken
Dim dtDate1 As Variant
Dim dtDate2 As Variant
Dim dtDiff As Variant
dtDiff = DateDiff("h", dtDate1, dtDate2)
This will give you the number of hours between two date/time values. Do some validation on the two original values and you should be able to figure out what you need. If you need more help let me know and I will do what I can!
HTH
Ken
MichaelRed
Programmer
The following is an approach which has been implemented. I would generally NOT embed the holidays within the code, but have done so here to provide the entire soloution within a single object. A much better approach tot he holidays wouls be to place them in a table, and to provide a maintenance facility to update them as necessary. I would also recommend that a check be made on the word dates vs. Holiday dates, to insure that the holiday dates include the entire range of work dates.
Additional enhancements which would be useful/necessary in a business / production environment would be to include variations in the working hours.
MichaelRed
m.red@att.net
There is never time to do it right but there is always time to do it over
Additional enhancements which would be useful/necessary in a business / production environment would be to include variations in the working hours.
Code:
Public Function basWrkHrs(StDate As Date, EndDate As Date) As Double
'Get the number of work HOURS between the given dates
Dim blnHoliFnd As Boolean 'Flag for Hloiday found
Dim Holidate(21) As Date 'Table of Holidays
Dim Idx As Long 'Index for start/end dates
Dim Kdx As Long 'Index / counter for Number of days
Dim Jdx As Integer 'Index doe the Hloidate array
Dim MyDate As Date 'Tempdate
Dim AccumTime As Double 'Hours Accumulated
Const MinsPerDay = 1440 'Every Minute of the DAY!!
Const MinsPerHr = 60# '60 Minutes per Hour
'For MAINTENANCE purposes, the array should be in a TABLE
'There SHOULD be a form to add/edit/delete the table.
'At run time, the TABLE should be wholy loaded into the ARRAY
'to promote execution effiency.
'Array(Table) of Holiday Dates
Holidate(0) = #1/1/2001# 'NewYearsDay
Holidate(1) = #1/17/2001# 'Martin Luther King Day
Holidate(2) = #2/2/2001# 'Groundhog Day
Holidate(3) = #2/12/2001# 'Lincon's Birthday
Holidate(4) = #2/14/2001# 'Valentine's Day
Holidate(5) = #2/21/2001# 'President's Day
Holidate(6) = #2/22/2001# 'Washington's Birthday
Holidate(7) = #3/8/2001# 'Ash Wednesday
Holidate(8) = #3/17/2001# 'St. Patrick's Day
Holidate(8) = #4/1/2001# 'April Fool's Day
Holidate(9) = #4/20/2001# 'Passover
Holidate(10) = #4/21/2001# 'Good Friday
Holidate(11) = #5/5/2001# 'Cinco de Mayo
Holidate(12) = #5/14/2001# 'Mother's Day
Holidate(13) = #6/11/2001# 'Pentecost
Holidate(14) = #6/18/2001# 'Father's Day
Holidate(15) = #7/4/2001# 'Independence Day
Holidate(16) = #9/4/2001# 'Labor Day
Holidate(17) = #10/31/2001# 'Halloween
Holidate(18) = #11/11/2001# 'Vetran's Day
Holidate(19) = #11/23/2001# 'Thanksgiving
Holidate(20) = #12/25/2001# 'Christmas
Holidate(21) = #12/31/2001# 'New Year's Eve
'Get the incremental Minutes for the Start & End Dates
If (Not (Weekday(StDate) = vbSaturday Or Weekday(StDate) = vbSunday)) Then
AccumTime = DateDiff("n", StDate, Format(StDate + 1, "mm/dd/yy"))
End If
If (Not (Weekday(EndDate) = vbSaturday Or Weekday(EndDate) = Sunday)) Then
AccumTime = AccumTime + DateDiff("n", Format(EndDate, "mm/dd/yy"), EndDate)
End If
MyDate = Format(StDate + 1, "Short Date")
'Loop for each day INSIDE the interval
For Idx = CLng(StDate + 1) To CLng(EndDate) - 1
blnHoliFnd = False
If (Weekday(MyDate) = vbSaturday Or Weekday(MyDate) = vbSunday) Then
blnHoliFnd = True
GoTo NoTime
End If
For Jdx = 0 To UBound(Holidate)
If (Holidate(Jdx) = MyDate) Then
blnHoliFnd = True
Exit For
' Else
' Do Nothing, it is NOT a Workday
End If
Next Jdx
NoTime:
'count WHOLE (Work) days
If (blnHoliFnd = False) Then
Kdx = Kdx + 1
End If
MyDate = DateAdd("d", 1, MyDate)
Next Idx
'Got the number of days. Now, add work minutes to acuumtime
AccumTime = AccumTime + CSng(Kdx) * CSng(MinsPerDay)
basWrkHrs = AccumTime / MinsPerHr
End Functionm.red@att.net
There is never time to do it right but there is always time to do it over
- Thread starter
- #6
Michael,
I am using a function very similar to this to drop non working days and give the hours difference between two dates, one question; the results from my function are used in a query - I then want to build a crosstab based on the values produced by the function, but they are treated as 'text' and therefore I cannot 'sum' or 'avg' on these calculated fields...
I have used convert to double (cdbl)(as I want .0 in the result), but still no avail - I have tried to return the result of the function as double also, but still cannot change the result from text to a number
any ideas .....
Thx
I am using a function very similar to this to drop non working days and give the hours difference between two dates, one question; the results from my function are used in a query - I then want to build a crosstab based on the values produced by the function, but they are treated as 'text' and therefore I cannot 'sum' or 'avg' on these calculated fields...
I have used convert to double (cdbl)(as I want .0 in the result), but still no avail - I have tried to return the result of the function as double also, but still cannot change the result from text to a number
any ideas .....
Thx
MichaelRed
Programmer
Lavey,
I have no idea why your procedure does ... After all, I can't see it. Mine does appear to return a double and be ammenable to use in aggregate functions.
Sameal, I (pseudo Sgt. Schultz) also know nothing about this. I do not choose/select a font for my posts, so it is just the default. It "loks like" courier to me, but I'm not big on fonts, so I could EASILY be wrong.
MichaelRed
m.red@att.net
There is never time to do it right but there is always time to do it over
I have no idea why your procedure does ... After all, I can't see it. Mine does appear to return a double and be ammenable to use in aggregate functions.
Sameal, I (pseudo Sgt. Schultz) also know nothing about this. I do not choose/select a font for my posts, so it is just the default. It "loks like" courier to me, but I'm not big on fonts, so I could EASILY be wrong.
MichaelRed
m.red@att.net
There is never time to do it right but there is always time to do it over
Michael,
I have attached my code below, I have even converted to double just before i return the result but still no joy...
I am I missing something vital here ?
Any input you have wuold be most helpful!
Public Function NonWorkingDays(ByVal DateIn As Date, ByVal DateOut As Date)
On Error GoTo CalcError:
Dim TotalHours As Long
Dim HoursToDrop As Long
Dim StartDate As Date
Dim EndDate As Date
'set up start/end dates/totalhours and reset hourstodrop to 0
StartDate = Format(DateIn, "general date"
EndDate = Format(DateOut, "general date"
TotalHours = DateDiff("n", StartDate, EndDate)
HoursToDrop = 0
Dim WeekStart As Byte
Dim WeekEnd As Byte
WeekStart = WeekDay(StartDate)
WeekEnd = WeekDay(EndDate)
Dim Monday As Date
Dim NewString As String
'if received and completed on sat or sun then calc hours only or set first working day to monday morning
Select Case WeekStart
Case 1
If WeekEnd = 1 And TotalHours < 1440 Then
NonWorkingDays = Format(TotalHours / 60, "0.0"
Exit Function
Else
NewString = StartDate + 1
NewString = Left(NewString, 8) & " 08:00"
Monday = NewString
HoursToDrop = DateDiff("n", StartDate, Monday)
End If
Case 7
If WeekEnd = 7 Or WeekEnd = 1 And TotalHours < 2880 Then
NonWorkingDays = Format(TotalHours / 60, "0.0"
Exit Function
Else
NewString = StartDate + 2
NewString = Left(NewString, 8) & " 08:00"
Monday = NewString
HoursToDrop = DateDiff("n", StartDate, Monday)
End If
End Select
'add up all sat/sun between startdate and end date
Dim DateCounter As Date
Select Case NewString
Case ""
DateCounter = StartDate
Do
If WeekDay(DateCounter) = 7 Or WeekDay(DateCounter) = 1 Then
HoursToDrop = HoursToDrop + 1440
End If
DateCounter = DateCounter + 1
Loop While DateCounter < EndDate
Case Else
DateCounter = NewString
Do
If WeekDay(DateCounter) = 7 Or WeekDay(DateCounter) = 1 Then
HoursToDrop = HoursToDrop + 1440
End If
DateCounter = DateCounter + 1
Loop While DateCounter < EndDate
End Select
'Too DAMN SLOOOOOOOOOOW with this piece of code!! was a dcount on holiday table
'Dim HoliDays As Integer
'
' HoliDays = DCount("holidate", "holidaytable", "(holidate Between #" & Format(StartDate, "short date" & "# And #" & Format(EndDate, "short date" & "#)"
' If Not HoliDays = 0 Then
' HoliDays = HoliDays * 1440
' HoursToDrop = HoursToDrop + HoliDays
' End If
'instead use an array created on app start
Dim J As Date
Dim I As Byte
Dim Holidays As Long
StartDate = Format(StartDate, "short date"
EndDate = Format(EndDate, "short date"
For J = StartDate To EndDate
For I = 0 To UBound(HoliArray) - 1
If Not WeekDay(J) = 1 Or WeekDay(J) = 7 Then
If HoliArray(I) = J Then
Holidays = Holidays + 1
End If
End If
Next I
Next J
If Holidays <> 0 Then
Holidays = Holidays * 1440
HoursToDrop = HoursToDrop + Holidays
End If
'true working hours is total hours minus the hours to be dropped - and there you go !
NonWorkingDays = TotalHours - HoursToDrop
If NonWorkingDays < 0 Then NonWorkingDays = NonWorkingDays + Holidays
NonWorkingDays = CDbl(Format(NonWorkingDays / 60, "0.0")
Exit Function
CalcError:
If Err.number = 94 Or Err.number = 13 Then
NonWorkingDays = Null
Else
DisplayMessage "Please report 'TR calc error - " & Err.number & " " & Err.Description & " '"
End If
End Function
Thx alot!!
I have attached my code below, I have even converted to double just before i return the result but still no joy...
I am I missing something vital here ?
Any input you have wuold be most helpful!
Public Function NonWorkingDays(ByVal DateIn As Date, ByVal DateOut As Date)
On Error GoTo CalcError:
Dim TotalHours As Long
Dim HoursToDrop As Long
Dim StartDate As Date
Dim EndDate As Date
'set up start/end dates/totalhours and reset hourstodrop to 0
StartDate = Format(DateIn, "general date"
EndDate = Format(DateOut, "general date"
TotalHours = DateDiff("n", StartDate, EndDate)
HoursToDrop = 0
Dim WeekStart As Byte
Dim WeekEnd As Byte
WeekStart = WeekDay(StartDate)
WeekEnd = WeekDay(EndDate)
Dim Monday As Date
Dim NewString As String
'if received and completed on sat or sun then calc hours only or set first working day to monday morning
Select Case WeekStart
Case 1
If WeekEnd = 1 And TotalHours < 1440 Then
NonWorkingDays = Format(TotalHours / 60, "0.0"
Exit Function
Else
NewString = StartDate + 1
NewString = Left(NewString, 8) & " 08:00"
Monday = NewString
HoursToDrop = DateDiff("n", StartDate, Monday)
End If
Case 7
If WeekEnd = 7 Or WeekEnd = 1 And TotalHours < 2880 Then
NonWorkingDays = Format(TotalHours / 60, "0.0"
Exit Function
Else
NewString = StartDate + 2
NewString = Left(NewString, 8) & " 08:00"
Monday = NewString
HoursToDrop = DateDiff("n", StartDate, Monday)
End If
End Select
'add up all sat/sun between startdate and end date
Dim DateCounter As Date
Select Case NewString
Case ""
DateCounter = StartDate
Do
If WeekDay(DateCounter) = 7 Or WeekDay(DateCounter) = 1 Then
HoursToDrop = HoursToDrop + 1440
End If
DateCounter = DateCounter + 1
Loop While DateCounter < EndDate
Case Else
DateCounter = NewString
Do
If WeekDay(DateCounter) = 7 Or WeekDay(DateCounter) = 1 Then
HoursToDrop = HoursToDrop + 1440
End If
DateCounter = DateCounter + 1
Loop While DateCounter < EndDate
End Select
'Too DAMN SLOOOOOOOOOOW with this piece of code!! was a dcount on holiday table
'Dim HoliDays As Integer
'
' HoliDays = DCount("holidate", "holidaytable", "(holidate Between #" & Format(StartDate, "short date"
& "# And #" & Format(EndDate, "short date" & "#)"' If Not HoliDays = 0 Then
' HoliDays = HoliDays * 1440
' HoursToDrop = HoursToDrop + HoliDays
' End If
'instead use an array created on app start
Dim J As Date
Dim I As Byte
Dim Holidays As Long
StartDate = Format(StartDate, "short date"
EndDate = Format(EndDate, "short date"
For J = StartDate To EndDate
For I = 0 To UBound(HoliArray) - 1
If Not WeekDay(J) = 1 Or WeekDay(J) = 7 Then
If HoliArray(I) = J Then
Holidays = Holidays + 1
End If
End If
Next I
Next J
If Holidays <> 0 Then
Holidays = Holidays * 1440
HoursToDrop = HoursToDrop + Holidays
End If
'true working hours is total hours minus the hours to be dropped - and there you go !
NonWorkingDays = TotalHours - HoursToDrop
If NonWorkingDays < 0 Then NonWorkingDays = NonWorkingDays + Holidays
NonWorkingDays = CDbl(Format(NonWorkingDays / 60, "0.0"
)Exit Function
CalcError:
If Err.number = 94 Or Err.number = 13 Then
NonWorkingDays = Null
Else
DisplayMessage "Please report 'TR calc error - " & Err.number & " " & Err.Description & " '"
End If
End Function
Thx alot!!
- Thread starter
- #11
Michael
The basWrkHrs function returns 25.5 as a response to
SELECT basWrkHrs(#11/20/2001 15:0:00#,#11/21/2001 16:30:00#) AS test;
but working hours in the day would be 9 to 5 so the total number of working hours would be 9.5. Is there any way to adapt the code to take this into account.
Its for a helpdesk project so i need to know how many working hours a job has been open for so i can update the priority of a job automatically.
Thanks again for your help
The basWrkHrs function returns 25.5 as a response to
SELECT basWrkHrs(#11/20/2001 15:0:00#,#11/21/2001 16:30:00#) AS test;
but working hours in the day would be 9 to 5 so the total number of working hours would be 9.5. Is there any way to adapt the code to take this into account.
Its for a helpdesk project so i need to know how many working hours a job has been open for so i can update the priority of a job automatically.
Thanks again for your help
- Thread starter
- #12
MichaelRed
Programmer
jamiec1.
There are numerous approaches to the overall issue. A part of the soloution is to just subtract the 17 hours per day NOT worked from the Const MinsPerDay = 1440 , the 1440 represents 24 (Hours) * 60 (Minutes), so just change the number of hours to YOUR workday * 60) for the minutes per day. The Start Day and End Day calculations are somewhat more contrived, as you need to calculate the difference between the Start date/time and the end of the working day for the first day's time and the difference between the start of the working day and the item completion date / time for the End Date.
I will show a SAMPLE of calculating the minutes in the Start day:
review this calculation. The dateadd("h", 17, ... adjusts the time (of te end of the day) to 5 PM. Note that the remainder of the calc remains unchanged. The calc for the last day needs to be modified to reflect your starting time -in an analagous manner.
I feel compeled to point out that this calculation is more 'fine grained' than is generally reasonable for the data available, You are generating 'information' essientially down to the minute, assuming that the work day is STRICTLY constant. This is simply NOT true. Breaks and interruptions occur continuously throughout the workday. Many of the interruptions are NOT generated by the work process, or they may be occassioned by a customer, so the calculation of 'how long it takes' for the task/item is always going to be in error - on the high side- wheather this error includes the 'dead tme' of non-working hours or not is really insignificant.
'++++++++++++++++++++++++++++++++++++++++++++++++++++++++
'++++++++++++++++++++++++++++++++++++++++++++++++++++++++
'++++++++++++++++++++++++++++++++++++++++++++++++++++++++
lavey,
I hardly know where to start. And perhaps I shouldn't. I DID glance at your code. I WILL NOT review it. The FIRST and most obvious issue is that you DO NOT declare the function return value type. This is douoble compounded by setting the function value to DIFFERENT data types, depending on where the value is "set". I noted three such places in a 15 second 'glance'.
The only other advice I can offer is not re 'your' code, but to either hire a 'real' programmer, or to take procedures from places like Tek-Tips which DO WORK, and modify them MINIMALLY to your needs.
I do not mean to be 'harsh', but I need to use my time more in the line of obtaining gainful employment. I do offer some help on Tek-Tips, but this is limited to efforts where I can do so quickly, such as posting routines which I already have (or can modify in just a few minutes), or to just point out an item for others to consider.
MichaelRed
m.red@att.net
There is never time to do it right but there is always time to do it over
There are numerous approaches to the overall issue. A part of the soloution is to just subtract the 17 hours per day NOT worked from the Const MinsPerDay = 1440 , the 1440 represents 24 (Hours) * 60 (Minutes), so just change the number of hours to YOUR workday * 60) for the minutes per day. The Start Day and End Day calculations are somewhat more contrived, as you need to calculate the difference between the Start date/time and the end of the working day for the first day's time and the difference between the start of the working day and the item completion date / time for the End Date.
I will show a SAMPLE of calculating the minutes in the Start day:
Code:
StDate = #11/05/2001 12:00 PM#
? DateDiff("n", StDate, dateadd("h", 17, Format(StDate, "mm/dd/yy")))
300review this calculation. The dateadd("h", 17, ... adjusts the time (of te end of the day) to 5 PM. Note that the remainder of the calc remains unchanged. The calc for the last day needs to be modified to reflect your starting time -in an analagous manner.
I feel compeled to point out that this calculation is more 'fine grained' than is generally reasonable for the data available, You are generating 'information' essientially down to the minute, assuming that the work day is STRICTLY constant. This is simply NOT true. Breaks and interruptions occur continuously throughout the workday. Many of the interruptions are NOT generated by the work process, or they may be occassioned by a customer, so the calculation of 'how long it takes' for the task/item is always going to be in error - on the high side- wheather this error includes the 'dead tme' of non-working hours or not is really insignificant.
'++++++++++++++++++++++++++++++++++++++++++++++++++++++++
'++++++++++++++++++++++++++++++++++++++++++++++++++++++++
'++++++++++++++++++++++++++++++++++++++++++++++++++++++++
lavey,
I hardly know where to start. And perhaps I shouldn't. I DID glance at your code. I WILL NOT review it. The FIRST and most obvious issue is that you DO NOT declare the function return value type. This is douoble compounded by setting the function value to DIFFERENT data types, depending on where the value is "set". I noted three such places in a 15 second 'glance'.
The only other advice I can offer is not re 'your' code, but to either hire a 'real' programmer, or to take procedures from places like Tek-Tips which DO WORK, and modify them MINIMALLY to your needs.
I do not mean to be 'harsh', but I need to use my time more in the line of obtaining gainful employment. I do offer some help on Tek-Tips, but this is limited to efforts where I can do so quickly, such as posting routines which I already have (or can modify in just a few minutes), or to just point out an item for others to consider.
MichaelRed
m.red@att.net
There is never time to do it right but there is always time to do it over
Michael,
Thx for your input, no offence taken! Any ideas are a route to investigate! I've only been writing code for about 6 months (I guess it shows!), I have written a quick adhoc to convert to double and it works - now I'll take a look at the bulk of this code and start over
I'm sure with the knowlegde you have it's not gonna take you long to get employemt (gainful !), your posts here are more than appreciated by us 'newbies'.
Thx!!
Thx for your input, no offence taken! Any ideas are a route to investigate! I've only been writing code for about 6 months (I guess it shows!), I have written a quick adhoc to convert to double and it works - now I'll take a look at the bulk of this code and start over
I'm sure with the knowlegde you have it's not gonna take you long to get employemt (gainful !), your posts here are more than appreciated by us 'newbies'.
Thx!!
- Thread starter
- #15
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Question