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.

"mhax" wrote:

>
> No one got a solution?
>
>
> --
> mhax
> ------------------------------------------------------------------------
> mhax's Profile: http://www.excelforum.com/member.php...o&userid=36450
> View this thread: http://www.excelforum.com/showthread...hreadid=562438
>
>

[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" wrote:

>
> No one got a solution?
>
>
> --
> mhax
> ------------------------------------------------------------------------
> mhax's Profile: http://www.excelforum.com/member.php...o&userid=36450
> View this thread: http://www.excelforum.com/showthread...hreadid=562438
>
>

[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]

"mhax" <[email protected]> wrote in message
news:[email protected]...
>
> 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.

--
Tim Williams
Palo Alto, CA

[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.

In article <[email protected]>,
"Tim Williams" <timjwilliams at gmail dot com> wrote:

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

[mhax]

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.

In article <[email protected]>,
"Tim Williams" <timjwilliams at gmail dot com> wrote:

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

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