Excel formula required that calculates the total time from start to finish for each date period

I have a spreadsheet that is used to calculate different jobs that my staff do as chauffeurs.

What I would like is a formula that looks at the date column, then the start column for the time work started then finds the row that contains the last input of that date and the finish time and calculates the number of hours on the job.

The thing is that on the spreadsheet there are multiple dates, so it would need to search and find the start and finish time of each date on the spreadsheet and calculate the number of hours on the job for each date, showing the total amount of time on the job for that particular spreadsheet (or the work shown on the spreadsheet).  

The total amount of hours should be shown in the format of [h}:mm and be displayed in cell AB6.

One spreadsheet would normal have about 150 plus jobs on it, over a 15 day period.  So we would use about 150 rows starting from Row A13.

Also, please do not convert or save the spreadsheet in xlsx.  Most of my workers still use the xls format.


For privacy reasons I have remove the prices and Client Names from the spreadsheet.
COPY-Chauffeur-SpreadSheet-Invoi.xls
WorldsplaygroundAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

byundtMechanical EngineerCommented:
Assuming that the dates and times are listed in ascending order, then you could use a worksheet formula like:
=TotalHours(D8,D9,A13:A63,G13:G63,H13:H63)
Results of this formula are a date/time serial number, and should be formatted as [h]:mm

This formula uses a user-defined function TotalHours, which is installed in Module1.
Function TotalHours(FirstDate As Double, LastDate As Double, Dates As Range, StartTimes As Range, FinishTimes As Range) As Double
'Returns the total number of hours worked between first start time on a day and last finish time, for every day between FirstDate and LastDate
Dim d As Double, dat As Double
Dim vStart As Variant, vFinish As Variant
Dim sDates As String, sFinish As String
sDates = Dates.Address(External:=True)
sFinish = FinishTimes.Address(External:=True)
For dat = FirstDate To LastDate
    Set vStart = Nothing
    Set vFinish = Nothing
    vStart = Application.Match(dat, Dates, 0)
    vFinish = Application.Evaluate("LOOKUP(2,IF(" & sDates & "=" & dat & "," & sFinish & ",""""))")
    If Not IsError(vStart) Then
        If Not IsError(vFinish) Then
            d = d + vFinish - StartTimes.Cells(vStart)
        End If
    End If
Next
TotalHours = d
End Function

Open in new window

Chauffeur-SpreadSheet-InvoiQ2823.xls
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
WorldsplaygroundAuthor Commented:
Absolutely great.  Works exactly the way I wanted it to.  You guys on Expert Exchange are sheer brilliant.  Best website and team of professionals I have ever come across.  Quick to respond and 9 out 10 times on the mark with success.

Thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.