Recording time between .....

alkarkar

Board Regular
Joined
Sep 18, 2005
Messages
125
This will be difficult for me to explain.
In cell A1 I get from a formula a name eg GEORGE .
In cell A2 I get from external source the current cpu time eg 10:12:34
Data updates every 0.5-1.5 sec (aprox)(not standard time)
At every update the conditions at cell A1 are calculated again and I get the names in that cell.
Eg.
NAME-------TIME
Bill--------10:12:34
Bill--------10:12:35
Bill--------10:12:37
Bill--------10:12:38
George---10:12:39
George---10:12:42
George---10:12:43
George---10:12:45
Tom-------10:12:48
Tom-------10:12:48
Tom-------10:12:49
George---10:12:50
George---10:12:52

In reality the name and the time are overwritten in cells A1,A2 and are not like a table as in the above example.

I want to record :
The first time a name is shown I need to get 0 value ( a new sequel of that name).
If the name is shown for the second time in a row I need the difference between these times.
If the name is shown for more than three times in a row I need the difference between the last appearance and the first.(the total time the name is shown)

The example:
NAME------TIME---------DIFF---------I NEED
Bill--------10:12:34-------0--------------0----------(first apear)
Bill--------10:12:35-------1--------------1
Bill--------10:12:37-------2--------------3
Bill--------10:12:38-------1--------------4---(10:12:38 minus 10:12:34)
George---10:12:39-------0--------------0-----------(first apear)
George---10:12:42-------3--------------3
George---10:12:43-------1--------------4
George---10:12:45-------2--------------6
Tom-------10:12:48-------0--------------0-----------(first apear)
Tom-------10:12:48-------0--------------0
Tom-------10:12:49-------1--------------1
George---10:12:50--------0--------------0-----------(first apear)
George---10:12:52--------2--------------2
I need the data from the last column lets say at Cell C1 (first the value 0,then 1,3,4,0,3,4,6,0,0,1,0,2). Every time a value is calculated at cell C1 I can record it using a macro (copy-paste it or is there is something better?).
It would be better if the diff between two times is given as a number and not time.
Example --- 2 and not 00:00:02.
The ideal would be not to use the time from cell A2 but every time this cell updates (from the external appl) to get the current cpu time with accuracy of milliseconds and get the time differences also with that accuracy ( this later ).
If I wasn’t clear please ask.
Thanks, Alexandros
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

venkat1926

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
I have slightly changed your data the firt row bill is the first appearance column C and D must be zero Now I opened new columns H and I and in these columns I have filled the formulas. I have introduced a header row.
see the fomula in H2 and I2. These formulas were copied down.
column I gives in seconds But remember the data should be sorted according to name and then time (column A and then column B) then only these formulas will give correct results.

check thorougly the results in columns H and I. after satisfying you can delete columns E,F and G

I noticed GEORGE IS REPEATED but are in different places. pl see my note in bold above

thes preadsheet with fomulas is given below.
Book1
ABCDEFGHIJK
1H1H2H3H4H5H6h41INSECONDS
2Bill10:12:3500FIRSTAPPEAR0 CHANGEDC1,D1,E1
3Bill10:12:37232 
4Bill10:12:3814(10:12:38minus10:12:34)33
5George10:12:3900(firstapear)0 
6George10:12:42333 
7George10:12:43144 
8George10:12:452666
9Tom10:12:4800(firstapear)0 
10Tom10:12:48000 
11Tom10:12:491111
12George10:12:5000(firstapear)0 
13George10:12:522222
Sheet3
 

venkat1926

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
I have slightly changed your data the firt row bill is the first appearance column C and D must be zero Now I opened new columns H and I and in these columns I have filled the formulas. I have introduced a header row.
see the fomula in H2 and I2. These formulas were copied down.
column I gives in seconds But remember the data should be sorted according to name and then time (column A and then column B) then only these formulas will give correct results.

check thorougly the results in columns H and I. after satisfying you can delete columns E,F and G

I noticed GEORGE IS REPEATED but are in different places. pl see my note in bold above. one way is to sort as I told earlier according to name and then time. But if you want to consder the appearance of george at row no. 12 (time 10 12 50) to be consdered as first apperance call him george1. that is what I have done.

thes preadsheet with fomulas is given below
Book1.xls
ABCDEFGHIJK
1H1H2H3H4H5H6h41INSECONDS
2Bill10:12:3500FIRSTAPPEAR0 CHANGEDC1,D1,E1
3Bill10:12:37232 
4Bill10:12:3814(10:12:38minus10:12:34)33
5George10:12:3900(firstapear)0 
6George10:12:42333 
7George10:12:43144 
8George10:12:452666
9Tom10:12:4800(firstapear)0 
10Tom10:12:48000 
11Tom10:12:491111
12George110:12:5000(firstapear)0 
13George110:12:522222seenamechange
14ifnameisnotchangedcolumnIwillgive13(i.e.101252-101239=13
Sheet3
 

alkarkar

Board Regular
Joined
Sep 18, 2005
Messages
125
Thanks for the response, it is not what i want because:
Data updates the values in cells A1,A2. No other cell is used (there is somewhere in my first post).
So available data is not in the form of a table. New data overwrites the past data in Cell A1,A2.
 

Forum statistics

Threads
1,136,649
Messages
5,676,989
Members
419,667
Latest member
MegEri

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top