Calculating tvl time in Excel 2000 Rev.9.2720

[Guest]

Can someone please help?
On timesheet in Excel, there are 4 columns:
Travel Time to site
Arrival time at site
Departure time from site
Return Travel time
If more than one job is done in a day, when traveling from a billable job to
a Maintenance job and then to another billable job and returning to
home/office, how can the return travel time be calculated for the two
billable jobs to arrive at the total time for the billable jobs that day?
Any assistance will be much appreciated. Thank you.
bg

 

[Myrna Larson]

I would add a column (E) headed "Billable" which contains Y or N. Then
assuming you have calculated the travel times in the 1st and 4th columns (say
that's A and D)

=SUMIF(E2:E100,"Billable",A2:A200)+SUMIF(E2:E100,"Billable",D2100)

 

[Guest]

Myrna, for some reason I got e-mail notification of your reply to Brandi's
problem. You might want to respond again in hopes that she will be notified
of your recommendation. -Jim Wood

 

[Myrna Larson]

Oops. I said to put Y or N in column E, so the formula should be

=SUMIF(E2:E100,"Y",A2:A200)+SUMIF(E2:E100,"Y",D2100)

 

[Myrna Larson]

I don't keep copies of messages already read. Sounds like I responded to your
response instead of to the original message. Hopefully she will return to the
ng to look for replies.

 

[Guest]

Myrna,
I added a "Billable" column and can total the hours for those jobs...Thank
you!

Now, here's what I'm having trouble with....
If a total of 9.0 hours are worked in a day, and following is the time
sequence:
For First Billable job:
"Tvl
Time
to site Arr Dep Rtn Tvl Billable Total Hours
07:00 08:00 10:30 11:00 Y 4:00
10:30 11:00 12:30 14:00 3:30
14:00 14:30 15:30 16:00 Y 2:00

There's an extra half-hour .5 (0.30) that's getting counted twice between
the 1st and 2nd job. Making the total hours worked 9.5 (9.30) instead of (9.0)
What can be done to fix it?