Home

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

%3CLINGO-SUB%20id%3D%22lingo-sub-683945%22%20slang%3D%22en-US%22%3ELooking%20for%20a%20solution%20to%20calculate%20total%20time%20form%20a%20arrive%20column%20and%20a%20depart%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-683945%22%20slang%3D%22en-US%22%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3EDriver%3C%2FTD%3E%3CTD%3EDate%3C%2FTD%3E%3CTD%3ETractor%3C%2FTD%3E%3CTD%3ETrailer%3C%2FTD%3E%3CTD%3ELocation%3C%2FTD%3E%3CTD%3EBillable%20Stop%3C%2FTD%3E%3CTD%3EDelivery%20BH%3C%2FTD%3E%3CTD%3EAppt%3C%2FTD%3E%3CTD%3EArrive%3C%2FTD%3E%3CTD%3EDepart%3C%2FTD%3E%3CTD%3EOdometer%3C%2FTD%3E%3CTD%3EDelivered%20Trays%3C%2FTD%3E%3CTD%3ETrays%20Picked%20Up%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ELundy%2C%20Jeffrey%3C%2FTD%3E%3CTD%3E6%2F2%2F19%3C%2FTD%3E%3CTD%3E852360%3C%2FTD%3E%3CTD%3E36406%3C%2FTD%3E%3CTD%3ESOMERSET%20DC%2FDEPOT%201%20WILEY%20DR%20SOMERSET%20%2C%20NJ%2008873-1222%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E7%3A00%3C%2FTD%3E%3CTD%3E7%3A30%3C%2FTD%3E%3CTD%3E469746%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ELundy%2C%20Jeffrey%3C%2FTD%3E%3CTD%3E6%2F2%2F19%3C%2FTD%3E%3CTD%3E852360%3C%2FTD%3E%3CTD%3E36406%3C%2FTD%3E%3CTD%3EGLEN%20BURNIE%20DEPOT%20889%20AIRPORT%20PARK%20RD%20GLEN%20BURNIE%20%2C%20MD%2021061-2555%3C%2FTD%3E%3CTD%3EDelivery%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E11%3A00%3C%2FTD%3E%3CTD%3E12%3A15%3C%2FTD%3E%3CTD%3E469935%3C%2FTD%3E%3CTD%3E812%3C%2FTD%3E%3CTD%3E420%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ELundy%2C%20Jeffrey%3C%2FTD%3E%3CTD%3E6%2F2%2F19%3C%2FTD%3E%3CTD%3E825360%3C%2FTD%3E%3CTD%3E36406%3C%2FTD%3E%3CTD%3ESOMERSET%20DC%2FDEPOT%201%20WILEY%20DR%20SOMERSET%20%2C%20NJ%2008873-1222%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E16%3A45%3C%2FTD%3E%3CTD%3E17%3A00%3C%2FTD%3E%3CTD%3E470125%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-683945%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-683982%22%20slang%3D%22en-US%22%3ERe%3A%20Looking%20for%20a%20solution%20to%20calculate%20total%20time%20form%20a%20arrive%20column%20and%20a%20depart%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-683982%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F354701%22%20target%3D%22_blank%22%3E%40Mike_4431%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhich%20total%3F%20Per%20date%2C%20per%20driver%2C%20etc.%20And%20what%20is%20in%20Depart%20column%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-684012%22%20slang%3D%22en-US%22%3ERe%3A%20Looking%20for%20a%20solution%20to%20calculate%20total%20time%20form%20a%20arrive%20column%20and%20a%20depart%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-684012%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20depart%20was%20time%20but%20I%20guess%20when%20I%20pasted%20something%20went%20off.%20I%20need%20the%20total%20by%20employee.%20Each%20%22trip%22%20is%20separated%20by%20a%20blank%20line.%20I%20attached%20a%20picture.%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F117817i19014620466FDCCD%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Screenshot_1.png%22%20title%3D%22Screenshot_1.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-684927%22%20slang%3D%22en-US%22%3ERe%3A%20Looking%20for%20a%20solution%20to%20calculate%20total%20time%20form%20a%20arrive%20column%20and%20a%20depart%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-684927%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F354701%22%20target%3D%22_blank%22%3E%40Mike_4431%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI'm%20not%20sure%20there%20you'd%20like%20to%20have%20your%20totals.%20If%20in%20blank%20row%20after%20the%20trip%2C%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20437px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F117831iD76F8BEC96C312B1%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ethat%20could%20be%3C%2FP%3E%0A%3CPRE%3E%3DSUMPRODUCT(MOD(%24J%242%3A%24J4-%24I%242%3A%24I4%2C1)*(%24A%242%3A%24A4%3D%24A4))%3C%2FPRE%3E%0A%3CP%3Ewhich%20is%20copy%2Fpasted%20on%20the%20cell%20after%20each%20trip.%20It%20calculates%20total%20time%20for%20the%20current%20employee%20for%20all%20trips%2Cif%20more%20than%20one%2C%20with%20cumulative%20effect.%20Cell%20id%20to%20be%20formatted%20as%20%5Bhh%5D%3Amm.%20MOD()%20here%20to%20calculate%20overnight%20time.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-684940%22%20slang%3D%22en-US%22%3ERe%3A%20Looking%20for%20a%20solution%20to%20calculate%20total%20time%20form%20a%20arrive%20column%20and%20a%20depart%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-684940%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20the%20help.%3C%2FP%3E%3CP%3EMike%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-684984%22%20slang%3D%22en-US%22%3ERe%3A%20Looking%20for%20a%20solution%20to%20calculate%20total%20time%20form%20a%20arrive%20column%20and%20a%20depart%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-684984%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F354701%22%20target%3D%22_blank%22%3E%40Mike_4431%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EMike%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E
Mike_4431
New Contributor
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  

 

5 Replies

@Mike_4431 

 

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

@Sergei Baklan 

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.Screenshot_1.png

@Mike_4431 

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

image.png

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.

Highlighted
Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies