How to calculate time intervals in excel

Asked

Viewed 2,106 times

0

I have a spreadsheet 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 one means he was in transit and the rest are factory codes. I need to calculate the time he was en route and the time he was in the factories but I have no idea how to do it. I appreciate the help.

excel

2 answers

3


You can create 2 auxiliary columns to facilitate understanding of the problem.

In one of the columns, you will check whether the COD.REVENDA of the line in question is equal to 1, and COD.REVENDA of the row above is equal to 1 also, if so, calculate the difference of times marked. And in the other column, will do the same for the COD.REVENDA = 2.

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

Copy and drag this formula so that in cells C10 and D10, the formulae are as follows::

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

And in cells G1 and G2, can paste the following formulas to have the total time spent on each of the activities:

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

Example:

inserir a descrição da imagem aqui

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 the conditions in Excel.

Try to implement in your case if you can’t help!

Browser other questions tagged excel

You are not signed in. Login or sign up in order to post.