Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
//time calculations
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
//time calculations
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 -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "sansk_23" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
//time calculations
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" wrote: 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 -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "sansk_23" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
//time calculations
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 -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "sansk_23" wrote in message ... 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" wrote: 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 -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "sansk_23" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
//time calculations
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 "Bernard Liengme" wrote: 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 -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "sansk_23" wrote in message ... 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" wrote: 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 -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "sansk_23" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Time Calculations | Excel Discussion (Misc queries) | |||
Time calculations - help please | Excel Discussion (Misc queries) | |||
Time difference calculations, daylight savings time, Excel | Excel Discussion (Misc queries) | |||
Overlapping time calculations and automatic time updates | Excel Worksheet Functions | |||
convert time imported as text to time format for calculations | Excel Worksheet Functions |