Count Working hour between time

[haviv]

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 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

 

[James A. Fortune]

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 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

Try the information in the following thread:

http://groups.google.com/group/comp...556dde02b88/44ed54b162a1aa3c#44ed54b162a1aa3c

James A. Fortune
(e-mail address removed)

 

[Ron2006]

If you starting time can NEVER be after 17:00 nor before 8:00
AND ending has the same constraint
AND the field 2 can never be greater than field 3.

Then if the start date = end date (just the date part) then the
subtract field 2 from field 3

ELSE

workdays = DateDiff("d",[StartDate],[EndDate]) - 1 (you want to use
ONLY the date part of the fields)
hours = workdays * 9
hours2 = #17:00# - datepart ("h",field2)
Hours2 = hours2 + (datepart ("h",field3) - #8:00#
Hours = hours + hours2

The above is basically true IF you work on weekends and holidays.

=============================
If you do not work on weekends then

WorkDays=DateDiff("d",[StartDate],[EndDate])-(DateDiff("ww",[StartDate],[EndDate
],7)+DateDiff("ww",[StartDate],[EndDate],1))


First datediff is total number of days between dates.
Second datediff is number of Saturdays between dates
Third datediff is number of Sundays between dates.

I believe you will also have to subtract 1 from workdays here to get
the proper count of full days used.

============================
If you do not work on Holidays
then you will need a table of holidays (the workday that would be taken
off inplace of the holiday - Monday if holiday is on Sunday)

Then use dCount on that table counting the number of dates in the table
between date part of field2 and field3 and subtract that from the
above computed workdays.

===========================

Ron

 

[James A. Fortune]

If you starting time can NEVER be after 17:00 nor before 8:00
AND ending has the same constraint
AND the field 2 can never be greater than field 3.

Then if the start date = end date (just the date part) then the
subtract field 2 from field 3

ELSE

workdays = DateDiff("d",[StartDate],[EndDate]) - 1 (you want to use
ONLY the date part of the fields)
hours = workdays * 9
hours2 = #17:00# - datepart ("h",field2)
Hours2 = hours2 + (datepart ("h",field3) - #8:00#
Hours = hours + hours2

The above is basically true IF you work on weekends and holidays.

=============================
If you do not work on weekends then

WorkDays=DateDiff("d",[StartDate],[EndDate])-(DateDiff("ww",[StartDate],[EndDate
],7)+DateDiff("ww",[StartDate],[EndDate],1))


First datediff is total number of days between dates.
Second datediff is number of Saturdays between dates
Third datediff is number of Sundays between dates.

I believe you will also have to subtract 1 from workdays here to get
the proper count of full days used.

============================
If you do not work on Holidays
then you will need a table of holidays (the workday that would be taken
off inplace of the holiday - Monday if holiday is on Sunday)

Then use dCount on that table counting the number of dates in the table
between date part of field2 and field3 and subtract that from the
above computed workdays.

===========================

Ron

Try the information in the following thread:

http://groups.google.com/group/comp...556dde02b88/44ed54b162a1aa3c#44ed54b162a1aa3c

James A. Fortune
(e-mail address removed)

I see that I read the post too quickly. Let me know if Ron's advice is
not enough.

James A. Fortune
(e-mail address removed)

 

[Guest]

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#

' 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