SOLVED

EXCEL FORMULA

%3CLINGO-SUB%20id%3D%22lingo-sub-2314443%22%20slang%3D%22en-US%22%3EEXCEL%20FORMULA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2314443%22%20slang%3D%22en-US%22%3E%3CP%3ECould%20anyone%20help%20me%20to%20find%20the%20total%20working%20hours%20of%20the%20below%20driver%20in%20excel%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22524%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22142%22%3EDUTY%20CODE%3C%2FTD%3E%3CTD%20width%3D%2287%22%3EROUTE%3C%2FTD%3E%3CTD%20width%3D%2290%22%3EPATTEN%3C%2FTD%3E%3CTD%20width%3D%22104%22%3EDEP%3A%20TIME%3C%2FTD%3E%3CTD%20width%3D%22101%22%3EARI%3A%20TIME%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EA001%3C%2FTD%3E%3CTD%20width%3D%2287%22%3EGD10%3C%2FTD%3E%3CTD%3EIN%3C%2FTD%3E%3CTD%20width%3D%22104%22%3E3%3A35%3C%2FTD%3E%3CTD%20width%3D%22101%22%3E4%3A20%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EA001%3C%2FTD%3E%3CTD%20width%3D%2287%22%3EGD10%3C%2FTD%3E%3CTD%20width%3D%2290%22%3EIN%3C%2FTD%3E%3CTD%3E4%3A35%3C%2FTD%3E%3CTD%20width%3D%22101%22%3E5%3A20%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EA001%3C%2FTD%3E%3CTD%20width%3D%2287%22%3EGD21%3C%2FTD%3E%3CTD%20width%3D%2290%22%3EIN%3C%2FTD%3E%3CTD%3E5%3A51%3C%2FTD%3E%3CTD%20width%3D%22101%22%3E6%3A20%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EA001%3C%2FTD%3E%3CTD%20width%3D%2287%22%3EGD10%3C%2FTD%3E%3CTD%20width%3D%2290%22%3EOUT%3C%2FTD%3E%3CTD%3E6%3A40%3C%2FTD%3E%3CTD%20width%3D%22101%22%3E7%3A20%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EA001%3C%2FTD%3E%3CTD%20width%3D%2287%22%3EGD09%3C%2FTD%3E%3CTD%20width%3D%2290%22%3EOUT%3C%2FTD%3E%3CTD%3E9%3A40%3C%2FTD%3E%3CTD%20width%3D%22101%22%3E10%3A20%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%3CSPAN%3E%3CBR%20%2F%3ETotal%20Working%20Hour%20%3D%2010%3A20-03%3A35%3D%2006%3A45hrs%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20any%20formula%20in%20excel%20to%20find%20the%20total%20working%20hours%20of%20a%20single%20person%20or%20a%20single%20duty%20card%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2314443%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2314543%22%20slang%3D%22en-US%22%3ERe%3A%20EXCEL%20FORMULA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2314543%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1042794%22%20target%3D%22_blank%22%3E%40sarunsaji31%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20think%20this%20will%20work%20as%20long%20as%20the%20driving%20time%20is%20less%20than%2024%20hours.%20I%20will%20play%20with%20this%2C%20but%20if%20the%20drive%20times%20are%20short%2C%20this%20may%20work.%20If%20I%20have%20misunderstood%20the%20request%2C%20please%20let%20me%20know%2C%20and%20I'll%20see%20if%20I%20can%20improve%20this.%20Good%20luck!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22612%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2268%22%3EDUTY%20CODE%3C%2FTD%3E%3CTD%20width%3D%2268%22%3EROUTE%3C%2FTD%3E%3CTD%20width%3D%2268%22%3EPATTEN%3C%2FTD%3E%3CTD%20width%3D%2268%22%3EDEP%3A%20TIME%3C%2FTD%3E%3CTD%20width%3D%2268%22%3EARI%3A%20TIME%3C%2FTD%3E%3CTD%20width%3D%2268%22%3EMinutes%20Depart%3C%2FTD%3E%3CTD%20width%3D%2268%22%3EMinutes%20Arrive%3C%2FTD%3E%3CTD%20width%3D%2268%22%3EDrive%20Time%20in%20Minutes%3C%2FTD%3E%3CTD%20width%3D%2268%22%3EIn%20Hours%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2268%22%3EA001%3C%2FTD%3E%3CTD%20width%3D%2268%22%3EGD10%3C%2FTD%3E%3CTD%20width%3D%2268%22%3EIN%3C%2FTD%3E%3CTD%20width%3D%2268%22%3E3%3A35%3C%2FTD%3E%3CTD%20width%3D%2268%22%3E4%3A20%3C%2FTD%3E%3CTD%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20215%3C%2FTD%3E%3CTD%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20260%3C%2FTD%3E%3CTD%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2045%3C%2FTD%3E%3CTD%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%200.75%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2268%22%3EA001%3C%2FTD%3E%3CTD%20width%3D%2268%22%3EGD10%3C%2FTD%3E%3CTD%20width%3D%2268%22%3EIN%3C%2FTD%3E%3CTD%20width%3D%2268%22%3E4%3A35%3C%2FTD%3E%3CTD%20width%3D%2268%22%3E5%3A20%3C%2FTD%3E%3CTD%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20275%3C%2FTD%3E%3CTD%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20320%3C%2FTD%3E%3CTD%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2045%3C%2FTD%3E%3CTD%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%200.75%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2268%22%3EA001%3C%2FTD%3E%3CTD%20width%3D%2268%22%3EGD21%3C%2FTD%3E%3CTD%20width%3D%2268%22%3EIN%3C%2FTD%3E%3CTD%20width%3D%2268%22%3E5%3A51%3C%2FTD%3E%3CTD%20width%3D%2268%22%3E6%3A20%3C%2FTD%3E%3CTD%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20351%3C%2FTD%3E%3CTD%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20380%3C%2FTD%3E%3CTD%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2029%3C%2FTD%3E%3CTD%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%200.48%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2268%22%3EA001%3C%2FTD%3E%3CTD%20width%3D%2268%22%3EGD10%3C%2FTD%3E%3CTD%20width%3D%2268%22%3EOUT%3C%2FTD%3E%3CTD%20width%3D%2268%22%3E6%3A40%3C%2FTD%3E%3CTD%20width%3D%2268%22%3E7%3A20%3C%2FTD%3E%3CTD%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20400%3C%2FTD%3E%3CTD%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20440%3C%2FTD%3E%3CTD%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2040%3C%2FTD%3E%3CTD%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%200.67%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2268%22%3EA001%3C%2FTD%3E%3CTD%20width%3D%2268%22%3EGD09%3C%2FTD%3E%3CTD%20width%3D%2268%22%3EOUT%3C%2FTD%3E%3CTD%20width%3D%2268%22%3E9%3A40%3C%2FTD%3E%3CTD%20width%3D%2268%22%3E10%3A20%3C%2FTD%3E%3CTD%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20580%3C%2FTD%3E%3CTD%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20620%3C%2FTD%3E%3CTD%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2040%3C%2FTD%3E%3CTD%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%200.67%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2314549%22%20slang%3D%22en-US%22%3ERe%3A%20EXCEL%20FORMULA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2314549%22%20slang%3D%22en-US%22%3Eload%20your%20file%20to%20see%20how%20you%20have%20organized%20workers%20data%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2314550%22%20slang%3D%22en-US%22%3ERe%3A%20EXCEL%20FORMULA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2314550%22%20slang%3D%22en-US%22%3EPlease%20see%20above%20-%20Drive%20time%20calculation.xlsx%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2314552%22%20slang%3D%22en-US%22%3ERe%3A%20EXCEL%20FORMULA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2314552%22%20slang%3D%22en-US%22%3E%3CP%3Edoes%20anything%20from%20this%20picture%20works%3F%3CBR%20%2F%3E%3CBR%20%2F%3Emake%20sure%20format%20is%20applied%20correctly%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2314555%22%20slang%3D%22en-US%22%3ERe%3A%20EXCEL%20FORMULA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2314555%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1042826%22%20target%3D%22_blank%22%3E%40CPadget%3C%2FA%3E%26nbsp%3B%20Hi%20there%20thanks%20for%20your%20support%2C%20please%20refer%20to%20the%20attached%20work%20data.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20that%20possible%20to%20calculate%20total%20working%20hours%20in%20a%20single%20formula%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2314560%22%20slang%3D%22en-US%22%3ERe%3A%20EXCEL%20FORMULA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2314560%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1042794%22%20target%3D%22_blank%22%3E%40sarunsaji31%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E-%20format%20as%20a%20tabe%3C%2FP%3E%3CP%3E-%20in%20little%20table%20aside%3C%2FP%3E%3CP%3E-%20%3Dunique%3CBR%20%2F%3E-%20%3Dmaxifs-minifs%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Could anyone help me to find the total working hours of the below driver in excel?

 

DUTY CODEROUTEPATTENDEP: TIMEARI: TIME
A001GD10IN3:354:20
A001GD10IN4:355:20
A001GD21IN5:516:20
A001GD10OUT6:407:20
A001GD09OUT9:4010:20


Total Working Hour = 10:20-03:35= 06:45hrs

 

Is there any formula in excel to find the total working hours of a single person or a single duty card?

 

 

10 Replies

@sarunsaji31 

 

I think this will work as long as the driving time is less than 24 hours. I will play with this, but if the drive times are short, this may work. If I have misunderstood the request, please let me know, and I'll see if I can improve this. Good luck!

 

DUTY CODEROUTEPATTENDEP: TIMEARI: TIMEMinutes DepartMinutes ArriveDrive Time in MinutesIn Hours
A001GD10IN3:354:20          215          260            45         0.75
A001GD10IN4:355:20          275          320            45         0.75
A001GD21IN5:516:20          351          380            29         0.48
A001GD10OUT6:407:20          400          440            40         0.67
A001GD09OUT9:4010:20          580          620            40         0.67
load your file to see how you have organized workers data
Please see above - Drive time calculation.xlsx

does anything from this picture works?

make sure format is applied correctly

@CPadget  Hi there thanks for your support, please refer to the attached work data.

 

Is that possible to calculate total working hours in a single formula?

 

@sarunsaji31 

- format as a tabe

- in little table aside

- =unique
- =maxifs-minifs

In the sheet you are not calculating brake time

First trip starts at 3:35 and finished at 4:20

After that next trip starts at 04:35 , between 4:20 to 4:35 few minutes gap(rest time) is there. Each duty having same

In your calculation brake time (gap or rest time) not considered

Above duty is only for one driver i just want to know how do i calculate multiple drivers duty hours in a single formula

best response confirmed by sarunsaji31 (Occasional Contributor)
Solution

work time for each driver including brake time, with helper column

Screenshot 2021-05-02 000447.png

@sarunsaji31 

@sarunsaji31 

 

You will find a suggested solution in the attached file.

 

Next time you want to ask (which we also want) please read this link in advance,

Welcome to your Excel discussion space!

it will save you and everyone who wants to help a lot of time.

 

I would be happy to know if I could help.

 

Nikolino

I know I don't know anything (Socrates)

 

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.

Dear Philip thanks for your support it's very useful.