No. of Hours Worked

[Pra]

Hi All,

Trying to calculate no. of hours a Truck worked each day in the attached spreadsheet. It has time for each transaction. So, the difference between maximum and minimum time is the hours worked each day. However I couldn't figure out a way so that the spreadsheet calculates automatically for each truck each day.

Any help is much appreciated.

Thanks.

 

[Nebu]

Hi:

Is this what you are looking for?

Thanks

 

[Pra]

That's just perfect Nebu. Appreciate your help.

Have a great weekend.

Thanks.

 

[John Jairo V]

Hi @Pra (and @Nebu)

Another option could be (no CSE):
=SUM(AGGREGATE({14;15},6,$E$2:$E$6960/(($A$2:$A$6960=A2)*($C$2:$C$6960=C2)),1)*{1;-1})

Blessings!

 

[Lori]

Great idea to generate {min,max} via aggregate function!

Not really any clearer but for interest's sake perhaps it's possible to save a couple of characters via the little used AVEDEV function, since:

sum({min,max}*{-1,1}) / 2 = avedev({min,max})

(I think one could also use stdev.p in place of avedev but this is posted from my cell so don't have a pc handy to test.)

 

[John Jairo V]

You're Right @Lori!!

Both functions working:

=2*AVEDEV(AGGREGATE({14;15},6,$E$2:$E$6960/(($A$2:$A$6960=A2)*($C$2:$C$6960=C2)),1))

and

=2*STDEV.P(AGGREGATE({14;15},6,$E$2:$E$6960/(($A$2:$A$6960=A2)*($C$2:$C$6960=C2)),1))

Blessings!