Here is a function that does what you want:
'************************************************* **********
Function computeHoursWorked(dtStart As Date, dtStop As Date) As Double
'************************************************* **********
Const tStart = #8:00:00 AM#
Const tStop = #5:00:00 PM#
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12482
' reset times to valid start or stop time, use minutes to allow for
partial hours
' must be between 0800 and 1700 inclusive (uses minutes to allow for
part hours)
If TimeValue(dtStart) < tStart Then dtStart = DateAdd("n", Hour(tStart)
* 60 + Minute(tStart), DateValue(dtStart))
If TimeValue(dtStart) > tStop Then dtStart = DateAdd("n", Hour(tStop) *
60 + Minute(tStop), DateValue(dtStart))
If TimeValue(dtStop) < tStart Then dtStop = DateAdd("n", Hour(tStart) *
60 + Minute(tStart), DateValue(dtStop))
If TimeValue(dtStop) > tStop Then dtStop = DateAdd("n", Hour(tStop) *
60 + Minute(tStop), DateValue(dtStop))
'compute difference and subtract extra invalid hours (eg. hours between
1700 and 0800)
' = overall hours difference between start and stop - invalid hours for
each complete day spanned (uses minutes to allow for part hours)
computeHoursWorked = DateDiff("n", dtStart, dtStop) / 60 - ((24 -
(DateDiff("n", tStart, tStop) / 60)) * DateDiff("d", dtStart, dtStop))
End Function
It can be stored in a module and referenced as part of a query:
e.g: SELECT User, dtStart, dtStop, ComputeHoursWorked([dStart],[dStop]) AS
HrsWorked FROM tbl_TimeCards;
You couild also reference it directly in a report:
e.g. = ComputeHoursWorked([dStart],[dStop]) ....... (where dStart
and dStop are valid fields in the report)
You would have to modify it to take into consideration Weekends or give
results in minutes instead of hours.
Brian
"haviv"
wrote in message
news:A42D4520-6C08-4F3A-95F6-2D32D21A75DB@microsoft.com...
> Hai,
>
> I am in the middle creating report in access to count working turn time. I
> have a series raw data consist of three coloum
>
> 1. User
> 2. Time and Date of Start working project
> 3. Time and Date of finished working project
> 4. Working hour from 08.00 - 17.00
>
> The issue here the user can start at anytime he like and finish at any
> time
> he like. But i need to count how many hours have he use to finish the
> project. I can create a logic to count. Issue here i have to be able
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12482
> create a
> logic or function than can cater below possible event
>
>
> Event one
> If project starts at 07.45 and finish at 08.45 then the turn time will be
> 45
> minute
> (08.45 minus 08.00)
>
> Event two
> If the projects starts at 08.45 and finish at 09.45 the turn time will be
> 1
> hour
> (09.45 minus 08.45)
>
> Event three
> If the project starts at 16:00 and finish at 18:00 the turn time will be 2
> hour
> (18.00 minus 16.00)
>
> Event four
> If the project starts at 16:00 today and finish at 09:00 the next day the
> turn time will be 2 hour as
> (17.00 minus 16.00 plus 09.00 minus 08.00)
>
> Event five
> If the project starts at 16:00 today and finish at 09:00 the next 2 day
> the
> turn time will be 10 hour
> (17.00 minus 16.00 plus 17.00 minus 08.00 plus 09.00 minus 08.00)
>
> You see i need a logic can cater all event the tricky part is in the even
> three and four which hard to be diffrentiate
>
> Please advise if any of you have a sample of project of a sample of logic
> that can cater all above event
>
> Sincerely Yours
> Haviv
Our Donation Goal
: Why Donate? : Donate Today! : Donate Anonymously (ਗੁਪਤ) : Our Family of Supporters 
Linear Mode
