EXCEL FORMULA

[sarunsaji31]

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?

[#2]

@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 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

[#3]

load your file to see how you have organized workers data

[#4]

Please see above - Drive time calculation.xlsx

[#5]

does anything from this picture works?

make sure format is applied correctly

[#6]

@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?

[#7]

@sarunsaji31 

- format as a tabe

- in little table aside

- =unique
- =maxifs-minifs

[#8]

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

[#9 · Accepted solution]

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

 

@sarunsaji31 

[#10]

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

[#11]

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