I need an expert for that!

[mhax]

This is what i got:
Date Time 1=on/0=off minutes
01-01-2006 07:00 1
03-01-2006 22:00 0 3780
04-01-2006 01:10 1
04-01-2006 23:30 0 1340
05-01-2006 06:10 1
15-01-2006 12:45 0 14795
16-01-2006 08:20 1
26-01-2006 13:50 0 14730
26-01-2006 16:40 1
31-01-2006 23:50 0 7630

This is what i need:

Date minutes
01-01-2006 1020
02-01-2006 1440
03-01-2006 1320
04-01-2006 ?
05-01-2006 ?
06-01-2006 ?
07-01-2006 ?
08-01-2006 ?
09-01-2006 ?
10-01-2006 ?
11-01-2006 ?
12-01-2006 ?
13-01-2006 ?
14-01-2006 ?
15-01-2006 ?
16-01-2006 ?
17-01-2006 ?
18-01-2006 ?
19-01-2006 ?
20-01-2006 ?
21-01-2006 ?
22-01-2006 ?
23-01-2006 ?
24-01-2006 ?
25-01-2006 ?
26-01-2006 ?
27-01-2006 ?
28-01-2006 ?
29-01-2006 ?
30-01-2006 ?
31-01-2006 ?

I want something in VB, or in formula if there is no VB idea!

 

[mhax]

No one got a solution

 

[Charlie]

Almost, I think, give me another 10 min.

 

[Charlie]

I presume your date/time stamp is in column "A", On/Off and number of minutes
(which I didn't need, as long as timestamps always occur in pairs) are in
columns "B" and "C". I put the output in columns "D" and "E", but guess
what? It failed on the 26th when there was a clock-out then a clock-in. I
ended up with two records for that day, but if this can get you started maybe
you can fix it.

That's all the time I can spend on this problem. Hope it helps.

Dim iRow As Long
Dim iBeg As Long
Dim iEnd As Long
Dim iStart As Long
Dim iStop As Long
Dim iTime As Long
Dim NewRow As Long
Dim LastRow As Long

LastRow = 10 ' find last row in column "A"

For iRow = 1 To LastRow Step 2

iBeg = DateDiff("n", 0, Format(Cells(iRow, 1), "dd-mm-yyyy hh:mm:ss"))
iEnd = DateDiff("n", 0, Format(Cells(iRow + 1, 1), "dd-mm-yyyy hh:mm:ss"))
iStart = (iBeg \ 1440 + 1) * 1440
iStop = (iEnd \ 1440 - 1) * 1440

NewRow = NewRow + 1
Cells(NewRow, 4) = DateAdd("n", iBeg, 0)
Cells(NewRow, 4).NumberFormat = "dd-mm-yyyy"
Cells(NewRow, 5) = iStart - iBeg

If iStop > iBeg Then
For iTime = iStart To iStop Step 1440
NewRow = NewRow + 1
Cells(NewRow, 4) = DateAdd("n", iTime, 0)
Cells(NewRow, 4).NumberFormat = "dd-mm-yyyy"
Cells(NewRow, 5) = 1440
Next iTime
NewRow = NewRow + 1
Cells(NewRow, 4) = DateAdd("n", iEnd, 0)
Cells(NewRow, 4).NumberFormat = "dd-mm-yyyy"
Cells(NewRow, 5) = iEnd - iStop - 1440
End If

Next iRow

 

[mhax]

hey! wow! really! thanks you!
good job!

yeah i saw that it doesnt work for the 26th! i need to sum when there
is more than 1 on/off per day!
Example :
01-01-2006 01:00:00 / 1
01-01-2006 02:00:00 / 0
01-01-2006 20:00:00 / 1
01-01-2006 22:00:00 / 0

So for the 1st!

01-01-2006 = 180 minutes

Actualy your macro is doing a very good job!
I will try to find a solution! But if you find it say it hehe!

Thanks you again!

 

[mhax]

_Date___________________Time_________On/off__Minutes__Minutes(in_the_same_day)_

2006-01-01 10:19:12 0 0,00 0,00
2006-01-01 11:23:02 1 585,72 585,72
2006-01-01 21:08:45 0 0,00 0,00
2006-01-02 00:13:45 1 625,35 625,35
2006-01-02 10:39:06 0 0,00 0,00
2006-01-02 11:31:45 1 649,88 649,88
2006-01-02 22:21:38 0 0,00 0,00
2006-01-03 00:32:53 1 0,15 0,15
2006-01-03 00:33:02 0 0,00 0,00
2006-01-03 00:44:12 1 653,67 653,67
2006-01-03 11:37:52 0 0,00 0,00
2006-01-03 12:39:06 1 629,83 629,83
2006-01-03 23:08:56 0 0,00 0,00
2006-01-04 01:29:48 1 657,32 657,32
2006-01-04 12:27:07 0 0,00 0,00
2006-01-04 13:27:12 1 650,32 632,80
2006-01-05 00:17:31 0 0,00 17,52
2006-01-05 02:23:35 1 657,67 657,67
2006-01-05 13:21:15 0 0,00 0,00
2006-01-05 14:42:43 1 654,23 557,28
2006-01-06 01:36:57 0 0,00 96,95
2006-01-06 03:08:38 1 667,38 667,38
2006-01-06 14:16:01 0 0,00 0,00
2006-01-06 15:37:00 1 664,80 503,00
2006-01-07 02:41:48 0 0,00 161,80
2006-01-07 03:32:08 1 689,70 689,70
2006-01-07 15:01:50 0 0,00 0,00
2006-01-07 15:57:46 1 1479,15 482,23
2006-01-08 16:36:55 0 0,00 996,92
2006-01-08 17:43:31 1 1428,30 376,48
2006-01-09 17:31:49 0 0,00 1051,82
2006-01-09 18:44:15 1 696,60 315,75
2006-01-10 06:20:51 0 0,00 380,85
2006-01-10 06:34:25 1 670,05 670,05
2006-01-10 17:44:28 0 0,00 0,00
2006-01-10 18:25:55 1 1495,60 334,08
2006-01-11 19:21:31 0 0,00 1161,52
2006-01-11 20:26:59 1 652,62 213,02
2006-01-12 07:19:36 0 0,00 439,60
2006-01-12 09:21:58 1 567,70 567,70
2006-01-12 18:49:40 0 0,00 0,00
2006-01-12 21:44:35 1 590,72 135,42
2006-01-13 07:35:18 0 0,00 455,30
2006-01-13 08:56:40 0 0,00 0,00
2006-01-13 09:11:30 0 0,00 0,00
2006-01-13 09:20:41 1 647,03 647,03
2006-01-13 20:07:43 0 0,00 0,00
2006-01-13 22:48:52 1 576,92 71,13
2006-01-14 08:25:47 0 0,00 505,78
2006-01-14 15:18:08 1 279,72 279,72
2006-01-14 19:57:51 0 0,00 0,00
2006-01-15 03:41:57 1 281,53 281,53
2006-01-15 08:23:29 0 0,00 0,00
2006-01-15 15:27:36 1 328,68 328,68
2006-01-15 20:56:17 0 0,00 0,00
2006-01-16 04:20:02 1 284,65 284,65
2006-01-16 09:04:41 0 0,00 0,00
2006-01-16 14:36:57 1 416,58 416,58
2006-01-16 21:33:32 0 0,00 0,00
2006-01-17 00:58:43 1 538,43 538,43
2006-01-17 09:57:09 0 0,00 0,00
2006-01-17 12:24:49 1 615,62 615,62
2006-01-17 22:40:26 0 0,00 0,00
2006-01-18 01:11:01 1 599,50 599,50
2006-01-18 11:10:31 0 0,00 0,00
2006-01-18 19:06:30 1 152,62 152,62
2006-01-18 21:39:07 0 0,00 0,00
2006-01-19 06:29:56 1 0,43 0,43
2006-01-19 06:30:22 0 0,00 0,00
2006-01-19 06:31:27 1 165,88 165,88
2006-01-19 09:17:20 0 0,00 0,00
2006-01-19 19:05:38 1 148,95 148,95
2006-01-19 21:34:35 0 0,00 0,00
2006-01-20 06:48:14 1 285,37 285,37
2006-01-20 11:33:36 0 0,00 0,00
2006-01-20 18:37:12 1 309,75 309,75
2006-01-20 23:46:57 0 0,00 0,00
2006-01-21 07:17:35 1 312,00 312,00
2006-01-21 12:29:35 0 0,00 0,00
2006-01-21 18:35:35 1 350,15 324,42
2006-01-22 00:25:44 0 0,00 25,73
2006-01-22 03:18:55 1 551,13 551,13
2006-01-22 12:30:03 0 0,00 0,00
2006-01-22 14:59:47 1 586,02 540,22
2006-01-23 00:45:48 0 0,00 45,80
2006-01-23 03:02:35 1 674,87 674,87
2006-01-23 14:17:27 0 0,00 0,00
2006-01-23 17:36:29 1 511,97 383,52
2006-01-24 02:08:27 0 0,00 128,45
2006-01-24 04:16:53 1 665,10 665,10
2006-01-24 15:21:59 0 0,00 0,00
2006-01-24 18:20:19 1 555,20 339,68
2006-01-25 03:35:31 0 0,00 215,52
2006-01-25 05:59:40 1 653,07 653,07
2006-01-25 16:52:44 0 0,00 0,00
2006-01-25 19:44:39 1 611,78 255,35
2006-01-26 05:56:26 0 0,00 356,43
2006-01-26 07:01:35 1 1,98 1,98
2006-01-26 07:03:34 0 0,00 0,00
2006-01-26 07:04:45 1 2,08 2,08
2006-01-26 07:06:50 0 0,00 0,00
2006-01-26 07:06:52 1 0,02 0,02
2006-01-26 07:06:53 0 0,00 0,00
2006-01-26 07:06:55 1 0,05 0,05
2006-01-26 07:06:58 0 0,00 0,00
2006-01-26 07:06:59 1 0,10 0,10
2006-01-26 07:07:05 0 0,00 0,00
2006-01-26 07:07:07 1 0,03 0,03
2006-01-26 07:07:09 0 0,00 0,00
2006-01-26 07:07:12 1 0,02 0,02
2006-01-26 07:07:13 0 0,00 0,00
2006-01-26 07:07:27 1 657,67 657,67
2006-01-26 18:05:07 0 0,00 0,00
2006-01-26 20:10:06 1 606,67 229,90
2006-01-27 06:16:46 0 0,00 376,77
2006-01-27 07:17:29 1 5124,10 1002,52
2006-01-30 20:41:35 0 0,00 *4121,58*
2006-01-30 23:30:43 1 880,23 29,28
2006-01-31 14:10:57 0 0,00 850,95
2006-01-31 14:10:57 1 0,05 0,05
2006-01-31 14:11:00 0 0,00 0,00
2006-01-31 14:11:07 1 0,15 0,15
2006-01-31 14:11:16 0 0,00 0,00
2006-01-31 14:11:18 1 444,17 444,17
2006-01-31 21:35:28 0 0,00 0,00

here's what i want:

Date Minutes

2006-01-01 585,72
2006-01-02 1275,23
2006-01-03 1283,65
2006-01-04 1290,12
2006-01-05 1232,47
2006-01-06 1267,33
2006-01-07 1333,73
2006-01-08 1373,40
2006-01-09 1367,57
2006-01-10 1384,98
2006-01-11 1374,53
2006-01-12 1142,72
2006-01-13 1173,47
2006-01-14 785,50
2006-01-15 610,22
2006-01-16 701,23
2006-01-17 1154,05
2006-01-18 752,12
2006-01-19 315,27
2006-01-20 595,12
2006-01-21 636,42
2006-01-22 1117,08
2006-01-23 1104,18
2006-01-24 1133,23
2006-01-25 1123,93
2006-01-26 1248,28
2006-01-27 1379,28
2006-01-28 *1440,00*
2006-01-29 *1440,00*
2006-01-30 1241,58
2006-01-31 1295,32

The macro from Excellent is working great, but i dont have seconds with
his macro! The second thing is that the macro doesnt sum for weird day
like the 26th where there is more than one on/off!

 

[mhax]

nobody else got an idea?

 

[Tim Williams]

nobody else got an idea?

About what?

We're not all using web forums (yuck), so it's best if you *quote* what you're referring to.

 

[JE McGimpsey]

I've never used Outlook Express, but I should think you'd be able to see
the reference header which would allow you to open the referenced post.

 

[mhax]

I've never used Outlook Express, but I should think you'd be able t
see
the reference header which would allow you to open the reference
post.

Ok well i didnt know about that thing! anyway i still didnt find th
solution for my problem, so i wish someone find it! thanks