I need an expert for that!
 

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
------------------------------------------------------------------------
mhax's Profile: http://www.excelforum.com/member.php...o&userid=36450
View this thread: http://www.excelforum.com/showthread...hreadid=562438

I need an expert for that!
 

No one got a solution

--
mha
-----------------------------------------------------------------------
mhax's Profile: http://www.excelforum.com/member.php...fo&userid=3645
View this thread: http://www.excelforum.com/showthread.php?threadid=56243

I need an expert for that!
 

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

I need an expert for that!
 

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

I need an expert for that!
 

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
------------------------------------------------------------------------
mhax's Profile: http://www.excelforum.com/member.php...o&userid=36450
View this thread: http://www.excelforum.com/showthread...hreadid=562438

I need an expert for that!
 

_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
------------------------------------------------------------------------
mhax's Profile: http://www.excelforum.com/member.php...o&userid=36450
View this thread: http://www.excelforum.com/showthread...hreadid=562438

I need an expert for that!
 

nobody else got an idea?


--
mhax
------------------------------------------------------------------------
mhax's Profile: http://www.excelforum.com/member.php...o&userid=36450
View this thread: http://www.excelforum.com/showthread...hreadid=562438

I need an expert for that!
 

"mhax" wrote in message
...

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

I need an expert for that!
 

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 ,
"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.

I need an expert for that!
 

JE McGimpsey Wrote:
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.

In article ,
"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 th
solution for my problem, so i wish someone find it! thanks

--
mha
-----------------------------------------------------------------------
mhax's Profile: http://www.excelforum.com/member.php...fo&userid=3645
View this thread: http://www.excelforum.com/showthread.php?threadid=56243