• 512K Members
• 3,410 Online
• 610K Conversations

New Contributor

# Looking for a solution to calculate total time form a arrive column and a depart column

 Driver Date Tractor Trailer Location Billable Stop Delivery BH Appt Arrive Depart Odometer Delivered Trays Trays Picked Up Lundy, Jeffrey 6/2/19 852360 36406 SOMERSET DC/DEPOT 1 WILEY DR SOMERSET , NJ 08873-1222 7:00 7:30 469746 Lundy, Jeffrey 6/2/19 852360 36406 GLEN BURNIE DEPOT 889 AIRPORT PARK RD GLEN BURNIE , MD 21061-2555 Delivery 11:00 12:15 469935 812 420 Lundy, Jeffrey 6/2/19 825360 36406 SOMERSET DC/DEPOT 1 WILEY DR SOMERSET , NJ 08873-1222 16:45 17:00 470125

5 Replies

# Re: Looking for a solution to calculate total time form a arrive column and a depart column

Which total? Per date, per driver, etc. And what is in Depart column?

# Re: Looking for a solution to calculate total time form a arrive column and a depart column

The depart was time but I guess when I pasted something went off. I need the total by employee. Each "trip" is separated by a blank line. I attached a picture.

# Re: Looking for a solution to calculate total time form a arrive column and a depart column

I'm not sure there you'd like to have your totals. If in blank row after the trip,

that could be

`=SUMPRODUCT(MOD(\$J\$2:\$J4-\$I\$2:\$I4,1)*(\$A\$2:\$A4=\$A4))`

which is copy/pasted on the cell after each trip. It calculates total time for the current employee for all trips,if more than one, with cumulative effect. Cell id to be formatted as [hh]:mm. MOD() here to calculate overnight time.

# Re: Looking for a solution to calculate total time form a arrive column and a depart column

Thank you for the help.

Mike

# Re: Looking for a solution to calculate total time form a arrive column and a depart column

Mike, you are welcome

Related Conversations