How to calculate time intervals in excel

Navigation
0

I have a worksheet that tells me where a truck was at a certain time: 

DATA/HORA      COD. REVENDA
24/1/17 0:50       1
24/1/17 0:51       1
24/1/17 0:53       1
24/1/17 0:54       1
24/1/17 0:56       1
24/1/17 0:57       1
24/1/17 0:59       2
24/1/17 1:00       2
24/1/17 1:04       2
24/1/17 1:07       2
24/1/17 3:00       1
24/1/17 3:00       1
24/1/17 3:15       1
24/1/17 3:25       1

Code 1 means that it was in transit and the others are factory codes. I need to calculate the time he got en route and how long he stayed at the factories but I have no idea how. Thanks for the help.

    
asked by anonymous 25.01.2017 / 14:57

2 answers

3

You can create 2 auxiliary columns to make it easier to understand the problem.

In one of the columns, you will check that the% w / o% of the row in question equals 1, and if the COD.REVENDA of the above row equals 1 also, if so, calculate the difference of the times marked . And in the other column, it will do the same for COD.REVENDA .

  • Cell C2: COD.REVENDA = 2
  • Cell D2: =SE(E(B2=1;B1=1);A2-A1;"")

Copy and drag this formula so that in the cells =SE(E(B2=2;B1=2);A2-A1;"") and C10 , the formulas are as follows:

  • Cell C10: D10
  • Cell D10: =SE(E(B10=1;B9=1);A10-A9;"")

And in cells =SE(E(B10=2;B9=2);A10-A9;"") and G1 , you can paste the following formulas to have the total amount of time spent in each of the activities:

  • G1: G2
  • G2: =SOMA(C:C)

Example:

    
25.01.2017 / 17:21
0

Lucas, this is easy, just understand how it works. I will pass a link that contains very simple examples for you to understand the conditions in Excel and adapt to your case.

Remember that to get the amount of time (hours) spent between two ranges, you can use something like: 

DATA/HORA    COD.REVENDA    TEMPO GASTO
0:50         1              =(A1 - HORARIO_INICIAL)
0:51         1              =(A2 - A1)
0:59         2              =(A3 - A2)

Below is an example of using conditions in Excel.

Try to implement in your case, if you can not help it!

25.01.2017 / 15:32
allowing only letters in the input with jquery Mask Open updated PDF