Help needed in analysing presence data

[tapcap]

Hi,

I'm trying to analyse room presence data with VBA. Raw data looks like this:
https://skydrive.live.com/?cid=5CDE0...0CD65C2E75!196
(1=presence, 0=No presence)
Now I need to edit data to hour precision (for example 01/02/2011 10 p.m., 01/02/2011 11 p.m, 01/03/2011 0 a.m, 01/03/2011 1 a.m...).

One way is to calculate presence in minutes and collect data to another table, where the weeks of the year (1-52) are in rows and week days and time (0 - 23 = 0 a.m - 11 p.m) are in colums. It looks like this:
https://skydrive.live.com/?cid=5CDE0...0CD65C2E75!202
An other option is to make table, where date and time are in A column and presence in B column
https://skydrive.live.com/?cid=5CDE0...0CD65C2E75!203

I already got one code from my friend that calculates presence in minute precision and collects data to year table. But it has a little bug. It doesn't work correctly and I don't know how to fix it. I think I know where the problem is. If presence (1) lasts to the next week (from sunday to monday), code doesn't change the week and goes back to the same week monday. (Week is from Monday to Sunday.) Code also doesn't understand if presence lasts multiple days.

So could someone help me to fix the code or to make a new one, please? I'm not so good with VBA and my friend isn't available.

Example file can be found here: https://skydrive.live.com/view.aspx?...D65C2E75%21204

And my friend's code:

Please Login or Register  to view this content.

Also posted: http://www.ozgrid.com/forum/showthread.php?t=159953