Function to add up total time taken?

[Tails]

Ok I'm posting this here as you guys have been so helpful in the past.

I've been asked to do a query to work out the total working hours taken to
do a job. I've done it as a function because its the only way I could
figure out.

Time taken should be from 8 til 8 five days a week - so far it SEEMS to work
but it's a bit buggy, and it only does 8 til 8 but includes weekends - I
want it to count only weekdays.

So if the job starts at 7pm on a friday and finishes at 10am on a monday
that's only three working hours.

This is what I've got so far, if anyone can give me any tips to get the
weekends working, or on what I've done wrong here I'd appreciate it!

(Oh, time is stored in a text field which I intend to strip out later,
because I want the time taken in HOURS, even if it took 115 hours and 30
minutes - not the equivalent in days/hours/minutes or whatever)

Thanks in advance!

--
Tails


'variable declarations
Dim timeTakenMins As Integer
Dim timeTakenHours As Integer
Dim timeTakenRemMins As Integer
Dim timeOne As Integer
Dim timeTwo As Integer
Dim timeWholeDays As Integer
Dim closingTime As Date
Dim openingTime As Date
Dim totalTime As String
Dim timeConvHours As String

Dim daysTaken As Integer

'declare closing and opening time - time stops being counted outside these
hours
closingTime = #8:00:00 PM#
openingTime = #8:00:00 AM#

'calculate days taken
daysTaken = DateDiff("d", dateEntered, dateCompleted)

'it is easy to work out time taken if only one day
If daysTaken = 0 Then
timeTakenMins = DateDiff("n", timeEntered, timeCompleted)
timeTakenHours = timeTakenMins \ 60 'returns a whole
number, no rounding
timeTakenRemMins = timeTakenMins Mod 60
timeConvHours = "00" 'keep all final
totals in the same format
totalTime = timeConvHours & ":" & timeTakenRemMins


'different method for one day
ElseIf daysTaken = 1 Then
timeOne = DateDiff("n", timeEntered, closingTime) 'time taken on day
one
timeTwo = DateDiff("n", openingTime, timeCompleted) 'time taken on day
two
timeTakenMins = timeOne + timeTwo 'total them up
timeTakenHours = timeTakenMins \ 60
timeTakenRemMins = timeTakenMins Mod 60
If timeTakenHours < 10 Then timeConvHours = "0" + timeTakenHours
'keep the final total in the same format
totalTime = timeTakenHours & ":" & timeTakenRemMins
'keep the final total in the same format


'different method for more than one day
ElseIf daysTaken > 1 Then
timeOne = DateDiff("n", timeEntered, closingTime) 'time taken on day
one
timeTwo = DateDiff("n", openingTime, timeCompleted) 'time taken on final
day
timeWholeDays = (daysTaken - 1) * 720 'twelve hours (720
mins) for each day in between
timeTakenMins = timeOne + timeTwo + timeWholeDays 'total them up
timeTakenHours = timeTakenMins \ 60
timeTakenRemMins = timeTakenMins Mod 60
timeConvHours = timeTakenHours
totalTime = timeConvHours & ":" & timeTakenRemMins 'keep it all in the
same format
Else
'unable to do anything as dates are wrong
'may include message box to state name of record incorrect?
End If

main = totalTime 'return total time

 

[John Nurick]

Hi Tails,

I'd use one of the ready-made working days functions from
http://www.mvps.org/access/datetime/date0006.htm . They do the hard work
(especially if you need to allow for public holidays), and the rest of
the code can be pretty simple, e.g. this (which hasn't been properly
tested and doesn't guard against incorrect inputs):

Function WkgHrs(Start As Date, Finish As Date) As Double
'Working hours between two times
Dim dtOpeningTime As Date 'start and end of working day
Dim dtClosingTime As Date
Dim dblHrs As Double 'accumulator

dtOpeningTime = #8:00:00 AM#
dtClosingTime = #8:00:00 PM#

If Int(Start) = Int(Finish) Then
'Start and finish on same day
dblHrs = (Finish - Start) * 24
Else
'If we're here, start and finish on different days.
'Hours on first day
dblHrs = (dtClosingTime - (Start - Int(Start))) * 24
'Hours on last day
dblHrs = dblHrs + (Finish - Int(Finish) - dtOpeningTime) * 24

'Hours on intervening days
dblHrs = dblHrs + (WorkingDays(Start, Finish)) _
* (dtClosingTime - dtOpeningTime) * 24
End If

WkgHrs = dblHrs
End Function