Calculate difference in hours spanning many days

[WolfPack]

I know I can calculate the difference in hours or days between two fields.
What I don't know is... can the DateDiff function calculate hours spanning
many days? Can I specify how long a day is or how long a week is?

I'm tracking time a document is spent being reviewed by engineers. This
document review can span many work days/weeks. I want to track the difference
of time (in hours) between two fields: TimeOut and TimeIn. But I need to
track hours (spanning numerous 8-hour days over 5-day weeks).

How do I do this?

 

[Randy]

Sure.

MsgBox DateDiff("h", "1/1/2006 13:33", "1/4/2006 13:33")

the datediff above should return a 72

 

[WolfPack]

What if the time spans over an 8-hour work day and a 5-day work week?

 

[WolfPack]

Okay... I'm new to this, so bear with me...

I created a module with the code you suggested. I then used the expression
builder to create an onClick event. Nothing happened. Then I added the the
datediff function after the calcWorkHours function. This got results, but it
returned an hour count as if the days we 24 hours and weeks were 7 days. It's
like my code is ignoring the calcWorkHours function all together.

What am I doing worng? I'm *very* new to creating functions and such, so
again... bear with me...

My code is as follows:

Private Sub Calculation_Click()
Calculation.Value = calcWorkHours(strtDate, endDate)
Calculation.Value = DateDiff("h", strtDate, endDate)
End Sub

 

[TC]

It sounds like you have mutiple records for the same document. Each
record has a time in, and a time out. The difference between the two,
is the time spent on that document (according to that particular
record). You want to add-up /all/ the records for a document, to find
the total time that was spent on it. I assume you'd also want to
seperate the data from different people reviewing the same document.

Yes?

If so, your table might look something like this:

tblReview
DocumentID } composite
PersonID } primary
DateTimeIn } key
DateTimeOut

The last two fields would be type Date. In Access, a Date field can
hold a complete date /and time/.

Then your SQL statement would look something like this:

SELECT DocumentID, SUM(DateDiff("?", DateTimeOut, DateTimeIn) AS
TotalTime
FROM tblReview
GROUP BY DocumentID

I can't remember the first DateDiff parameter choices, hence the
question mark.

HTH,
TC (MVP Access)
http://tc2.atspace.com

 

[Randy]

Sorry WolfPack left out a very import statement in the code below.
calcWorkHours = lngHrs
**The problem is that the function really isn't returning anything.**

Ok try this & see if this helps to shed some light on it for ya.

On a blank form drop a textbox and a command button on it.
name the textbox txtCalculation
name the command button cmdCalculate
make cmdCalculate_click() look like this:

Private Sub cmdCalculate_Click()
Dim myCalc As Long

myCalc = calcWorkHours("1/1/2006", "1/15/2006")
Me.txtCalculation.Value = myCalc
End Sub

and the calcWorkHours should look like the one below.

Function calcWorkHours(strtDate As Date, endDate As Date) As Long
Dim lngHrs As Long
Dim lngDys As Long

Do While strtDate < endDate
If Weekday(strtDate) <> 1 And Weekday(strtDate) <> 7 Then
lngDys = lngDys + 1

End If
strtDate = strtDate + 1
Loop
lngHrs = lngDys * 8
calcWorkHours = lngHrs
End Function

Hope it helps

Randy

 

[WolfPack]

Thanks Randy. It works great. Sorry for the delay. I've been busy with other
work.

 

[WolfPack]

Okay... One more for ya!

What if I need the time calculation to take into account the time of day?
That is... if strtDate and endDate spans two days, this function calculates
16 hours (unless over a weekend).

But what if the strtDate and endDate are date stamped by checkboxes, and the
time of day is a critical factor? If I check strtDate at 8:00 AM Monday, and
endDate at 9:00 AM Tuesday, the calculation logs 8 hours, not 9. Likewise, if
the strtDate and endDate diff is less than 1 day, then the calculated result
= 0.

How do I account for fractions of a day in hours?