//time calculations

[sansk_23]

Hi !! I have a table as below :

Date Time IN Date Time Out No. of hrs
01/09/2008 23:30 02/09/2008 1:30 2.00
02/09/2008 22:00 03/09/2008 2:30 4.30
03/09/2008 23:00 04/09/2008 0:30 1.30
04/09/2008 0:20 05/09/2008 0:50 0.30
05/09/2008 1:00 05/09/2008 2:00 1.00
06/09/2008 2:00 06/09/2008 3:00 1.00
07/09/2008 3:00 07/09/2008 4:00 1.00
08/09/2008 4:00 08/09/2008 5:00 1.00

Each day i record the vehicle in & out time at the warehouse.
1.) How can i automatically calculate the no of hrs the vehicle was at the wh.
2.) A also need the total no of hrs for a select period.
3.) How do i calculate the average time vehicle reaches the wh for a period.
4.) How do i calculate the average time vehicle leaves the wh for a period.
Simple average is not giving the correct result for IN-TIME & OUT-TIME ,
since in some case there is a change in the dates as well. Vehicle come in
the night but leaves post midnight when the date has also changed.
Pls. help me with this problem.
regards
Sansk_23

 

[Bernard Liengme]

Assuming your dates to be in format dd/mm/yyyy (otherwise your mechanics are
very slow!)
this formula =(D1-B1+(C1-A1))*24 computes the time for first vehicle
Excel treats time as a fraction of a day.
I get values 2. 4.5, 1.5, 24.5, 1, 1, 1, 1 where 4.5 is 4 and 1/2 hours (not
4 h 5 min)

In J1 I entered a date (01/09/2008) and in K 1 I entered another
(05/09/2008)
In L1 I used =SUMPRODUCT(--(A1:A8>=J1),--(A1:A8<=K1)) to tell me how many
vehicles were in during that period (5)
The total time for that period is given by
=SUMPRODUCT(--(A1:A8>=J1),--(A1:A8<=K1),E1:E8) ---the answers is 33.5
The average time is L1/M1 (6.7 hours)

For more details on SUMPRODUCT
Bob Phillips
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
J.E McGimpsey
http://mcgimpsey.com/excel/formulae/doubleneg.html

best wishes

 

[sansk_23]

Hi !! Thanks.
But, as desired in points 3 & 4,
i wanted the avergae IN-TIME & average OUT-TIME for the vehicles for a
select period.
And, simple avergae of column B & D does not give the correct avergae time,
because in some case the date changes.
If you could pls help me resolve this as well.
eg - if you average the data in column B, it gives - 9:51 (which doesnt make
any sense) since the vehicles are reaching the wh in the night.
rgds
Sansk_23

 

[Bernard Liengme]

I used the formula =A1+B1 in G1 to add time to date
Copied this down to G8
In G9 I used =AVERAGE(G1:G8) to get 04/09/2008 21:51

If you do not want to devote a column to this use the single formula
=AVERAGE(A1:A8+B1:B8)
but commit it with SHIFT+CTRL+ENTER not just ENTER. Also Excel will not know
this is a date/time so you will need to give it a format like dd/mm/yyyy
h:mm

best wishes

 

[sansk_23]

Hi. Thx.

Just wanted to check that if i make it a point to give the format as
dd/mm/yyyy
h:mm , in the column B & D -

1.) Do i still need to mention the date separately in the column A & C ?
2.) Does the formula for calculating the no. of hrs =(D1-B1+(C1-A1))*24 ,
also need to be changed / amended , or should give the same results ?

rgds
Sansk_23