| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Need expert VBA help | Excel Discussion (Misc queries) | |||
| Is anyone an expert? | Excel Discussion (Misc queries) | |||
| What is an expert? | Excel Discussion (Misc queries) | |||
| Excel Expert | Excel Programming | |||
| Expert help needed | Excel Programming | |||
![mhax[_4_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
