Data parcer

[deviniska]

Hello, gurus.
I've been writing a macros that would import and process some data in excel. Until i've stuck at making a parser, that will among other things would just parse through a three column sheet adding and subtracting data from variables.
So I have several sheets consisted of this data:

1	14.01.2014	8:20:07
2	14.01.2014	13:25:09
3	14.01.2014	13:38:14
2	14.01.2014	16:34:10
3	14.01.2014	17:34:32
4	14.01.2014	18:49:40

<tbody>
</tbody>

(if interesting, these are logon and logoff times as well as system lock and unlock events on the PC)
what i need is by passing to the function a month variable (01.2014) to calculate the exact time between event 1 (logon) and 2 (lock), 3(unlock) and 2, 1 and 4(logoff). so that the function just sum up all the "active" times by subtracting time stamps.
(Algorithm as follows: OK, computer, i got 1 (or 3) in cell A1, and 2 (or 4) in cell A2, and both of them according to B1 and B2 happened on January 2014, so C2-C1 equals 5:05:02. and so on...)
A bonus error check: if somehow the sequence would be broken and after event 1 another event 1 (or event 3) would happen, there could be another variable with +1 counter, so that i can evaluate the amount of errors.
I've parsed through this forum myself, but not being able to make a valid code, hope you can help somehow.
Thanks in advance.

 

[shawnhet]

Hi there,

Welcome to the board!

A couple of questions: How is the data above formatted? Is it all text or are there numbers in one or more columns?

Also, is it possible for something to go from 2 to 4 directly without going through 3?

For your count of errors, am I correct in assuming an error happens every time the computer locks (2)?

Finally, how do you want your output to look - do you want a single result for the whole month or a running total?

Cheers,

 

[deviniska]

Hello! Thanks for support!
- Columns formatted as a number / date / time accordingly
- Theoretically yes, but is should be discarded, as no "active time" is recorded.
- an error happens if two similar events happening one after another. It is a simple logic. When user logs on, he can lock the computer or log off. anything else means, that some event was missed out. Also a mistake would be if logon event would be followed by unlock event (this means that Lock event was missed out).Since we need only "active" time after logon and after unlock, that only counter would +1 and the active time would remain the same. I hope i made myself clear.
As data will be imported per month I assume, that the result for the month will be optimal.

hope you got the idea thanks!!!