Forum Discussion

Mike_4431's avatar
Mike_4431
Copper Contributor
Jun 11, 2019

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

DriverDateTractorTrailerLocationBillable StopDelivery BHApptArriveDepartOdometerDelivered TraysTrays Picked Up
Lundy, Jeffrey6/2/1985236036406SOMERSET DC/DEPOT 1 WILEY DR SOMERSET , NJ 08873-1222  7:007:30469746  
Lundy, Jeffrey6/2/1985236036406GLEN BURNIE DEPOT 889 AIRPORT PARK RD GLEN BURNIE , MD 21061-2555Delivery 11:0012:15469935812420
Lundy, Jeffrey6/2/1982536036406SOMERSET DC/DEPOT 1 WILEY DR SOMERSET , NJ 08873-1222  16:4517:00470125  

 

excel

5 Replies

    • Mike_4431's avatar
      Mike_4431
      Copper Contributor
      Jun 11, 2019

      SergeiBaklan 

      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.

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Jun 11, 2019

        Mike_4431 

        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.

Resources