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
Highlighted
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.

Related Conversations
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
Tabs and Dark Mode
cjc2112 in Discussions on
22 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