SOLVED

# EXCEL FORMULA

Occasional Contributor

# EXCEL FORMULA

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

 DUTY CODE ROUTE PATTEN DEP: TIME ARI: TIME A001 GD10 IN 3:35 4:20 A001 GD10 IN 4:35 5:20 A001 GD21 IN 5:51 6:20 A001 GD10 OUT 6:40 7:20 A001 GD09 OUT 9:40 10: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

# Re: EXCEL FORMULA

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 CODE ROUTE PATTEN DEP: TIME ARI: TIME Minutes Depart Minutes Arrive Drive Time in Minutes In Hours A001 GD10 IN 3:35 4:20 215 260 45 0.75 A001 GD10 IN 4:35 5:20 275 320 45 0.75 A001 GD21 IN 5:51 6:20 351 380 29 0.48 A001 GD10 OUT 6:40 7:20 400 440 40 0.67 A001 GD09 OUT 9:40 10:20 580 620 40 0.67

# Re: EXCEL FORMULA

Please see above - Drive time calculation.xlsx

# Re: EXCEL FORMULA

does anything from this picture works?

make sure format is applied correctly

# Re: EXCEL FORMULA

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

# Re: EXCEL FORMULA

- format as a tabe

- in little table aside

- =unique
- =maxifs-minifs

# Re: EXCEL FORMULA

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

# Re: EXCEL FORMULA

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

# Re: EXCEL FORMULA

You will find a suggested solution in the attached file.

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.

# Re: EXCEL FORMULA

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