Spreading Formulas across the whole document
 

I have a document that it has 35 thousand entries of different times. I need
to figure out the time intervals between every single entry. The formula i
use is pretty simple =(H#-H#). For example how would i adjust that formula so
it takes in account all the 30 something thousand of other rows.

My thing looks something like this
Time Interval
12:01:03.396 AM
12:01:06.309 AM
12:02:25.823 AM
12:02:30.195 AM
12:03:49.709 AM


Is there a formula that i can get the difference between each time and add
it up for a total amount of hours....

please any advice is VERY appreciate it

Thank You

Spreading Formulas across the whole document
 

Are you looking for a cumulative time from the first entry to whatever entry
is on the row you're looking for? If so, lock the first time down with the
F9 key in your formula.

For example, assuming the first piece of data is in A1, here are the formulas:

12:01:03 AM
12:01:06 AM 0:00:03 =A2-$A$1
12:02:26 AM 0:01:22 =A3-$A$1
12:02:30 AM 0:01:27 =A4-$A$1
12:03:50 AM 0:02:46 =A5-$A$1

Type the first formula, then copy it all the way. The $A$1 won't change,
but the other references will.

"Vlad" wrote:

I have a document that it has 35 thousand entries of different times. I need
to figure out the time intervals between every single entry. The formula i
use is pretty simple =(H#-H#). For example how would i adjust that formula so
it takes in account all the 30 something thousand of other rows.

My thing looks something like this
Time Interval
12:01:03.396 AM
12:01:06.309 AM
12:02:25.823 AM
12:02:30.195 AM
12:03:49.709 AM


Is there a formula that i can get the difference between each time and add
it up for a total amount of hours....

please any advice is VERY appreciate it

Thank You

Spreading Formulas across the whole document
 

=SUMPRODUCT((B1:I10<"")*(B1:I10-A1:H10))

adjust to suit, but note how the subtracted ranges are offset by a column

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Vlad" wrote in message
...
I have a document that it has 35 thousand entries of different times. I
need
to figure out the time intervals between every single entry. The formula i
use is pretty simple =(H#-H#). For example how would i adjust that formula
so
it takes in account all the 30 something thousand of other rows.

My thing looks something like this
Time Interval
12:01:03.396 AM
12:01:06.309 AM
12:02:25.823 AM
12:02:30.195 AM
12:03:49.709 AM


Is there a formula that i can get the difference between each time and add
it up for a total amount of hours....

please any advice is VERY appreciate it

Thank You

Spreading Formulas across the whole document
 

Oh, and format the results cell as [h]:mm

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Vlad" wrote in message
...
I have a document that it has 35 thousand entries of different times. I
need
to figure out the time intervals between every single entry. The formula i
use is pretty simple =(H#-H#). For example how would i adjust that formula
so
it takes in account all the 30 something thousand of other rows.

My thing looks something like this
Time Interval
12:01:03.396 AM
12:01:06.309 AM
12:02:25.823 AM
12:02:30.195 AM
12:03:49.709 AM


Is there a formula that i can get the difference between each time and add
it up for a total amount of hours....

please any advice is VERY appreciate it

Thank You

Spreading Formulas across the whole document
 

Thank for your quick respone Bob, but i am not sure what to substitute for
the B1 and I10 and H10, if you can please explain that to me..


I also wanted to clarify something....just in case you guys did not understand
The time goes on for around 34 thousand rows. Since the Date spands from
June 18th to Feb 28th. I wanted to find out what is the time interval between
the later time and the earlier time. So for example in this case

12:01:03.396 AM
12:01:06.309 AM
12:02:25.823 AM
12:02:30.195 AM
12:03:49.709 AM

12:01:06.309 - 12:01:03.396 = Answer
12:02:25.823 - 12:01:06.309 = Answer
12:02:30.195 - 12:02:25.823 = Answer
12:03:49.709 - 12:02:30.195 = Answer
---------
Total

Now the total i am pretty sure how to get, the hard part for me is figuring
out a formula that will span over the long document and that will the higher
time and subtract it by the lower time just like i showed in the
example......this might be impossible since i am not a Excel guru....but any
help or hint that will direct me to the right track would be much appreciated!

Tahnk You






"Bob Phillips" wrote:

Oh, and format the results cell as [h]:mm

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Vlad" wrote in message
...
I have a document that it has 35 thousand entries of different times. I
need
to figure out the time intervals between every single entry. The formula i
use is pretty simple =(H#-H#). For example how would i adjust that formula
so
it takes in account all the 30 something thousand of other rows.

My thing looks something like this
Time Interval
12:01:03.396 AM
12:01:06.309 AM
12:02:25.823 AM
12:02:30.195 AM
12:03:49.709 AM


Is there a formula that i can get the difference between each time and add
it up for a total amount of hours....

please any advice is VERY appreciate it

Thank You

Spreading Formulas across the whole document
 

A1 will be the first cell with a time, and B1 will be the cell to the right.
I1 will be the last cell on the FIRST ROW with data in, H1 will be the cell
to the left of that.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Bob Phillips" wrote in message
...
=SUMPRODUCT((B1:I10<"")*(B1:I10-A1:H10))

adjust to suit, but note how the subtracted ranges are offset by a column

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Vlad" wrote in message
...
I have a document that it has 35 thousand entries of different times. I
need
to figure out the time intervals between every single entry. The formula
i
use is pretty simple =(H#-H#). For example how would i adjust that
formula so
it takes in account all the 30 something thousand of other rows.

My thing looks something like this
Time Interval
12:01:03.396 AM
12:01:06.309 AM
12:02:25.823 AM
12:02:30.195 AM
12:03:49.709 AM


Is there a formula that i can get the difference between each time and
add
it up for a total amount of hours....

please any advice is VERY appreciate it

Thank You

Spreading Formulas across the whole document
 

bump....can anyone please help

"Vlad" wrote:

I have a document that it has 35 thousand entries of different times. I need
to figure out the time intervals between every single entry. The formula i
use is pretty simple =(H#-H#). For example how would i adjust that formula so
it takes in account all the 30 something thousand of other rows.

My thing looks something like this
Time Interval
12:01:03.396 AM
12:01:06.309 AM
12:02:25.823 AM
12:02:30.195 AM
12:03:49.709 AM


Is there a formula that i can get the difference between each time and add
it up for a total amount of hours....

please any advice is VERY appreciate it

Thank You