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
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies