Difference In Time

  • Thread starter Thread starter James
  • Start date Start date
J

James

I have one colum that has the time(hour:minute:second) and cell underneath
has a second time some seconds later. I need to find out the difference in
time (in seconds) and place this value in a seperate cell. The cell
underneath has a time, and I need to find the difference in that cell and
the one under it, and put it in a seperate cell....and it contiunes for
thousands of times. How would I go about doing this?
 
Hi James,
A2: 23:00:15
A3: 23:00:45 B3: =A3-A2 format as time

If you might insert/remove lines later change
B3: =A3-OFFSET(A3,-1,0)

If the times might run through midnight (see my datetime.htm page)
B3: =A3-A2 + (A2>A3)
modified with offset so you can insert/remove lines later
B3: =A3-OFFSET(A3,-1,0) + (OFFSET(A3,-1,0)>A3)
reason for use of OFFSET in
http://www.mvps.org/dmcritchie/excel/offset.htm

If you want the answer in decimal seconds you would format
as decimal and multiply by 86400 which is 24*60*60
to convert difference in days to seconds.

If the question is also one of copying the formulas down,
that is done with the fill handle. Grab the blob to the right of
a selected cell in this case A3 with the mouse and drag down.
http://www.mvps.org/dmcritchie/excel/fillhand.htm
You can double click on the fill handle if your A column goes
down for thousands of cells. Another way is to use the name box
to the left of the formula bar: B3:B4005 then use Ctrl+D
to fill down http://www.mvps.org/dmcritchie/excel/shortx2k.htm
 
well Right now A1 is 12:30:12 AM
A2 is 12:30:24 AM
B3 =A2-A1 which =12:30:12 AM...., I want it to equal 12...
 
Ok, I reformated and it seems to work now with the a2-a1

Ok, yes I also need to fill in thousands of cells with these values, but I
want it to be a2-1 with the answer in the cell to the right, then a4-a3
with the answer to the right, so basically only second line will have a
value, not all of them...
How do I do this?
 
It's not 12, it's 12 seconds.What would you like it to say if A2 was
12:32:24 ?

Format the cell as time (no AM/PM suffix) to see 00:00:12
Or format as general and multiply by 86400 to see 12

HTH. Best wishes Harald
 
Hi James,
Glad you got it working how you wanted, but please try
to be more specific when telling us what your problem is.
want it to be a2-1 with the answer in the cell to the right,
Click to expand...

Maybe to an English major (lawyer) that makes sense, but I don't
know what is to the right of what and that is why I gave
examples with addresses and formulas.

And I presume you meant =A2-A1
but it is questionable to me if that would be
B1: =A2-A1
or
B2: =A2-A1
though it really shouldn't matter much to you, because you
should be able to pick up on *where* you want the formula
and to adapt the formula to your use.
It makes things a lot easier to answer if you tell us specifically
what you have and want, rather than forcing us to read through a
paragraph to try to figure out what could have been written
with addresses and values and addresses and desired results.
(and what you tried that didn't work)

I started at A2 with the first number instead of A1 to allow you
to put titles on the first row, but the examples work the same way.

The fill handle works the same whether you start with a
formula in B1, B2, or B3 and fill down.

Anyway, you found everything and worked it out, don't
forget what I wrote about modifying formulas if your
times go through midnight, and modifying formulas if
you might possibly be deleting and/or inserting times
from the middle.

The more time you spend working out the question, the
easier it will be to answer. If you do a real good job at explaining
the problem. You might even find that you can answer most of
your own questions (even it is just happens to as soon as you
hit the send key).

Good luck with your future problems.
 
Back
Top