Time between 2 dates

[macroapa]

Hi,

I need to write a query that return the number of hours between 2
dates/times. However, I only want to count working hours and therefore
exclude any hours after 5pm and before 8am and also the whole of sat/
sun/bank hols.

Is there a custome function anywhere that acheives this?

Cheers

 

[Jörn Bosse]

Am 07.06.2010 22:30, schrieb macroapa:
Well,

i just wrote you those 2 Functions. But they´re only without Saturday
and Sundays caus i´m from germany and i really don´t know when all your
holidays are sorry for that.

Public Function WorkHours(Date1 As Date, Date2 As Date) As Long
Dim intZ As Integer
Dim intX As Integer
intZ = DateDiff("d", Date1, Date2, vbMonday)
intX = ((intZ - SaSoOut(Date1, Date2)) * 9)
WorkHours = intX
End Function

Public Function SaSoOut(Date1 As Date, Date2 As Date) As Long
Dim D1 As Date
Dim i As Long
If Date2 > Date1 Then
D1 = Date1
i = DateDiff("D", D1, Date2)
Else
D1 = Date2
i = DateDiff("D", D1, Date1)
End If
SaSoOut = (Weekday(D1, vbMonday) + i) \ 7 + _
(Weekday(D1, vbSunday) + i) \ 7
End Function

Call it in query like this:

HoursTotal: WorkHours(DateColumn1,DateColumn2)

Hope this helps you a little bit

Chears
Jörn

 

[macroapa]

Many thanks for the reply, but it doesn't quite do what I'm after.

If Date1 = 07/06/2010 16:00
and Date 2 = 08/06/2010 08:00

then it should return 1 as there is 1 working hour between the 2 as I
want to exclude any hours after 5pm and before 8am.

The code provided returns 9 as it seems to calc that there is 1
working day between the 2 dates and multiplies by 9 as there is 9
working hours in a full day.

Thanks again for you help

 

[Jörn Bosse]

Sorry i just thought that your members work every day 9 hours.
In your case i really don´t have a single idea how to solve this
problem. I´m sorry that i can´t help you.
But i´m not sure why you need the weekend and the holydays in that case.
It seems like you have to add an entry every single day, don´t you?

Regards
Jörn


Am 08.06.2010 00:52, schrieb macroapa: