Work hours Function

[mr moe]

I have a function that is calculating the hourse between two times, it's working fine for example time1 11:00am vs time2 12:00 pm, it gives me 1 hour, the only time it doesnt work is when the time difference is less than 1 hour, anything that is less than one hour i get 0, any advice? thanks.

 

[pbaldy]

Might have helped if you'd posted your function, but I assume one of these will work for you:

http://www.pacificdb.com.au/Support/code_diff2dates.htm

http://www.pacificdb.com.au/Support/code_difftime.htm

 

[jzwp22]

Is the function the built-in function in Access (datediff()) or a custom function? If it is a custom function, you will need to post the code. In this case, I would just use the datediff() function rather than something custom.

If you are using the datediff() function, the function returns a long integer value assuming that you are using the hours interval option "h". As such, you will get a zero if the time difference is less than 1 hour. I would use minutes option and then convert to hours (or fractional hours)

datediff("n", time1field, time2field)/60

 

[mr moe]

Here is the code, as i said it gives me accurate resutls but whenever the total hours between two dates is less than one hour it gives me zero, the funcation i'm using is in a query. Here is the code.

Dim intGrossDays As Integer
Dim intGrossHours As Single
Dim dteCurrDate As Date
Dim i As Integer
Dim WorkDayStart As Date
Dim WorkDayEnd As Date
Dim nonWorkDays As Integer
Dim StartDayhours As Single
Dim EndDayhours As Single

NetWorkhours = 0
nonWorkDays = 0

'-- Make sure that the input days are within working hours

'Calculate work day hours on 1st and last day
WorkDayStart = DateValue(dteStart) + TimeValue("08:00:00 AM")
WorkDayEnd = DateValue(dteStart) + TimeValue("05:00:00 PM")


If dteStart < WorkDayStart Then
dteStart = WorkDayStart
End If
If dteStart > WorkDayEnd Then
dteStart = WorkDayEnd
End If
WorkDayStart = DateValue(dteEnd) + TimeValue("08:00:00 AM")
WorkDayEnd = DateValue(dteEnd) + TimeValue("05:00:00 PM")
If dteEnd = Null Then
dteEnd = WorkDayStart
End If
If dteEnd < WorkDayStart Then
dteEnd = WorkDayStart
End If
If dteEnd > WorkDayEnd Then
dteEnd = WorkDayEnd
End If
'-- Now calculate the beginning and ending day hours
WorkDayStart = DateValue(dteEnd) + TimeValue("08:00:00 AM")
WorkDayEnd = DateValue(dteStart) + TimeValue("05:00:00 PM")
StartDayhours = DateDiff("h", dteStart, WorkDayEnd)
EndDayhours = DateDiff("h", WorkDayStart, dteEnd)

'adjust for time entries outside of business hours
'If StartDayhours < 0 Then
' StartDayhours = 0
'End If
'
'If EndDayhours > 8 Then
' EndDayhours = 8
'End If

'Calculate total hours and days between start and end times
intGrossDays = DateDiff("d", (dteStart), (dteEnd))
intGrossHours = DateDiff("h", (dteStart), (dteEnd))

'count number of weekend days and holidays (from a table called "Holidays" that lists them)
For i = 0 To intGrossDays
dteCurrDate = dteStart + i
If Weekday(dteCurrDate, vbSaturday) < 3 Then
nonWorkDays = nonWorkDays + 1
Else
If Not IsNull(DLookup("[Holidate]", "Holidays", "[Holidate] = #" & Int(dteCurrDate) & "#")) Then
nonWorkDays = nonWorkDays + 1
End If
End If
Next i
'Calculate number of work hours

Select Case intGrossDays
Case 0
'start and end time on same day
NetWorkhours = intGrossHours
Case 1
'start and end time on consecutive days
NetWorkhours = NetWorkhours + StartDayhours
NetWorkhours = NetWorkhours + EndDayhours
Case Is > 1
'start and end time on non consecutive days
NetWorkhours = NetWorkhours - (nonWorkDays * 1)
NetWorkhours = (intGrossDays - 1 - nonWorkDays) * 8
NetWorkhours = NetWorkhours + StartDayhours
NetWorkhours = NetWorkhours + EndDayhours

End Select

 

[mr moe]

I think the problem is in the query, it is not showing the decimales, how can i make it do that?

 

[mr moe]

I have used a minutes function then converted the total time to hour, but this is also giving me wrong results. Like for 60 minutes i'm seeing 660, and like 570 ect.. here is the code.

'-- 480 minutes per day and up to 480 minutes for the beginning and ending day


Dim intWorkingDays As Integer
Dim WorkDayStart As Date
Dim WorkDayEnd As Date
Dim StartDayMinutes As Single
Dim EndDayMinutes As Single
Dim StartDayMinutes2 As Single

NetWorkMinutes = 0

'Calculate work day hours on 1st and last day
WorkDayStart = DateValue(dteStart) + TimeValue("08:00:00 AM")
WorkDayEnd = DateValue(dteStart) + TimeValue("05:00:00 PM")
If dteStart < WorkDayStart Then
dteStart = WorkDayStart
End If
If dteStart > WorkDayEnd Then
dteStart = WorkDayEnd
End If
WorkDayStart = DateValue(dteEnd) + TimeValue("08:00:00 AM")
WorkDayEnd = DateValue(dteEnd) + TimeValue("05:00:00 PM")
If dteEnd < WorkDayStart Then
dteEnd = WorkDayStart
End If
If dteEnd > WorkDayEnd Then
dteEnd = WorkDayEnd
End If

'Calculate work day minutes on 1st and last day
WorkDayStart = DateValue(dteEnd) + TimeValue("08:00:00 AM")
WorkDayEnd = DateValue(dteStart) + TimeValue("05:00:00 PM")
StartDayMinutes = DateDiff("n", dteStart, WorkDayEnd)
StartDayMinutes2 = DateDiff("n", dteStart, dteEnd)
EndDayMinutes = DateDiff("n", WorkDayStart, dteEnd)

intWorkingDays = WorkingDays(DateValue(dteStart), DateValue(dteEnd))

'Calculate number of work hours
Select Case intWorkingDays
Case 0
'start and end time on same day
NetWorkMinutes = StartDayMinutes2
Case 1
'start and end time on consecutive days
NetWorkMinutes = StartDayMinutes + EndDayMinutes
Case Is > 1
'start and end time on non consecutive days
NetWorkMinutes = StartDayMinutes + EndDayMinutes + ((intWorkingDays - 1) * 480)

End Select

 

[Brianwarnock]

A bit confusing as you calculate hours and the minutes for the same time frame, also what is WorkingDays is it a function akin to Networkdays in Excel if so it is inclusive so you need to -2.

Also why build
WorkDayStart = DateValue(dteStart) + TimeValue("08:00:00 AM")

use Timevalue in the Datediff


StartDayMinutes=DateDiff("n", TimeValue(dtestart), #05:00:00 PM#)

What was the 570 instead of

Brian

 

[jzwp22]

I assume that your function is called NetWorkhours, but you did not include what datatype the function returns integer or single etc. (Public Function NetWorkHours() as ?)

 

[Brianwarnock]

I assume that your function is called NetWorkhours, but you did not include what datatype the function returns integer or single etc. (Public Function NetWorkHours() as ?)

Yes I find it annoying that so often the first line is missing when code is quoted leaving us to guess what is being passed and how as well as the function declaration as you indicate.

Brian

 

[mr moe]

Sorry here is the first line for the first function
Public Function NetWorkhours(dteStart As Date, dteEnd As Date) As Double


here is the first line for the second function
Public Function NetWorkMinutes(dteStart As Date, dteEnd As Date) As Double

FYI the date formate for the start and end is in this way: 3/31/11 11:00 PM


To be honest I think this is not a fucntion issue it's mostl likely a data issue, i have used the fucntion before and it was working fine. After i started to get the data from different sources I started to have problems all at suddent.

 

[jzwp22]

I copied your code to a new database and was playing around with it. If I use the NetWorkMinutes() function with the start and end dates separated by only 45 minutes, I get the correct # of minutes. If I supply the same dates to the NetWorkHours() I get 0 hours as expected. You mentioned that everything worked fine but you were getting data from a different source. So what do the date/time values look like in the new source?

 

[mr moe]

Well I agree with what your saying but here is what I'm getting when i run the networkminutes fucntion between two dates. Please dont assume that I have to divide by 10 because it might work for some numbers but not the others. Thanks. As i mentioned before could it be my dates's fomrate????? Thanks sir.
Onsite Start Date Onsite Stop Date Downtime
3/4/11 10:00 AM 3/4/11 11:00 AM 600
3/1/11 12:53 PM 3/1/11 1:50 PM 597
3/3/11 2:00 PM 3/3/11 3:00 PM 600
3/3/11 3:40 PM 3/3/11 4:15 PM 575
3/3/11 1:09 PM 3/3/11 1:45 PM 576
3/4/11 11:45 AM 3/4/11 1:15 PM 630
3/5/11 12:30 PM 3/5/11 1:47 PM 617
3/8/11 4:26 PM 3/8/11 5:26 PM 574
3/8/11 10:00 AM 3/8/11 12:00 PM 660
3/9/11 1:30 PM 3/9/11 5:00 PM 750
3/8/11 12:15 PM 3/8/11 1:00 PM 585

 

[jzwp22]

When I was working with your code yesterday I was using Access 2010 and everything seemed to work OK (but probably wasn't). While at work today, I created a new database in Access 2003 and got compile errors on several statements. The error was as follows:
Compile Error: Function call on left hand side of assignment must return Variant or Object)

The following lines were highlighted

In networkminutes():

NetWorkhours = 0


In networkhours():

NetWorkMinutes=0


If I comment out the above statements then it errors on the next statement where you try to assign a value to the function (within the other) such as this one within NetWorkHours

NetWorkMinutes = StartDayMinutes2

If you call a function from within another function, the appropriate way to do that is to use:

call functionname(date1, date2)

I don't think that you can assign a value to another function while in a current function, you can only call the other function.

So now we need to understand what these functions were intended to do.

 

[Brianwarnock]

It looks like all of your times have 540 minutes added to them, thats 9 hours, or 1 day and 1 hour, maybe that helps.

As I said I found the code presented confusing plus I stll don't know what workingdays is, is it a 2007/2010 function?

as per
intWorkingDays = WorkingDays(DateValue(dteStart), DateValue(dteEnd))




Brian

 

[mr moe]

HI Brain, Thank you so much for your help, what you are saying makes sense thanks yes it is addding 540 minutes, I think it's probably adding a whole day which i shouldnt', what makes me confused is that this is working perfectly on a different database. Regarding your question if this is 2007/2010 I dont know for sure what you mean, actually this is a function i got from a forum in this site. Thanks for your help. I think somewhere when it's a non consecutive dates i probably need to subract 540 minutes.

 

[Brianwarnock]

If its this 1 from Chergh then its starts with 1 for start and end dates equal, like networkdays in Excel as I stated or asked earlier, so you need to adjust your days, but why 540mins your days are 480, another 60 to find.

Brian

Public Function WorkingDays(StartDate As Date, EndDate As Date) As Long


Dim intCount As Long

intCount = 0

Do While StartDate <= EndDate

Select Case Weekday(StartDate)
   Case Is = 1, 7
      intCount = intCount
   Case Is = 2, 3, 4, 5, 6
      intCount = intCount + 1
End Select

StartDate = StartDate + 1

Loop

WorkingDays = intCount

End Function

 

[jzwp22]

I assumed that mr moe was using the workingdays function from this site.

 

[Brianwarnock]

That function has options which could still result in 1 day being returned not 0, its a pity that we did not have all the information, also its a puzzle that it works on another DB, what's the difference?

Brian

 

[jzwp22]

I am wondering if in Access 2010 it saw the function names as just another variable rather than as a function name. I cannot remember if I had set Option Explicit which would have flagged the variable as not being defined & possibly seeing that it was actually the name of an existing function. I will have to check on that this evening.

I did add the Option Explicit this morning when I put the functions into an Access 2003 database here at work.

 

[Brianwarnock]

If its this 1 from Chergh then its starts with 1 for start and end dates equal, like networkdays in Excel as I stated or asked earlier, so you need to adjust your days, but why 540mins your days are 480, another 60 to find.

Brian

The penny dropped whilst gardening yesterday, myguess from wayback is correct but the statement above not quite. If the function returns an inclusive day count , which I believe it does, then starting and finishing on the same day will action case 1 thus times from 12 - 1 pm will give 17:00-12:00=5hrs and 13:00-08:00 =5hrs Total 600 minutes instead of 60, this principle will apply to all, also all other calculations will be out.

I was thrown by the fact that Mr Moe multiplies complete days by 480, obvoiusly allowing 1 hour for lunch? , but he also needs to do this on the first and last and only 1 day , ie 10am till 4 pm is not 6 but 5 hours, and starting before lunch on day 1 will will cost 1 hr as will finishing after lunch on the last day.

I leave Mr Moe to correct his code.

Brian