Macro:Vb:elapsed time per day

**mhax** · 07-17-2006, 12:08 PM

Hi

I really need some help with my problem's. I will try to keep it simple.

2006-01-26 18:05:07 0
2006-01-26 20:10:06 1
2006-01-27 06:16:46 0
2006-01-27 07:17:29 1
2006-01-30 20:41:35 0
2006-01-30 23:30:43 1
2006-01-31 14:10:57 0

I have that in an excel file. 1 mean the thing's is open, 0 mean the thing's is not. We can see that there is not a values for each days. In another sheet, i have (in this case) all day 2006 january

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

Like this. I would like to past in that sheet the amount of time in minutes that the thing have been open. Example : 2006-01-27 07:17:29 1
2006-01-30 20:41:35 0
That mean's the thing have been open for 3 days, but i need a macro that will put for january 28, 1440 minutes, and january 29, 1440 minutes, and all the minutes for the other days!! Which you guys understand me :P! Ask some clarifications if needed :P!
Thanks you!

excelent · 07-17-2006, 04:05 PM

it dosent make eny sense to me

what is thing's - where to put minutes?
the things u wrote dont tell me when things is open or when its closed

try explain in other words what u looking for

"mhax" skrev:

>
> Hi
>
> I really need some help with my problem's. I will try to keep it
> simple.
>
> 2006-01-26 18:05:07 0
> 2006-01-26 20:10:06 1
> 2006-01-27 06:16:46 0
> 2006-01-27 07:17:29 1
> 2006-01-30 20:41:35 0
> 2006-01-30 23:30:43 1
> 2006-01-31 14:10:57 0
>
> I have that in an excel file. 1 mean the thing's is open, 0 mean the
> thing's is not. We can see that there is not a values for each days. In
> another sheet, i have (in this case) all day 2006 january
>
> 2006-01-01
> 2006-01-02
> 2006-01-03
> 2006-01-04
> 2006-01-05
> 2006-01-06
> 2006-01-07
> 2006-01-08
> 2006-01-09
> 2006-01-10
> 2006-01-11
> 2006-01-12
> 2006-01-13
> 2006-01-14
> 2006-01-15
> 2006-01-16
> 2006-01-17
> 2006-01-18
> 2006-01-19
> 2006-01-20
> 2006-01-21
> 2006-01-22
> 2006-01-23
> 2006-01-24
> 2006-01-25
> 2006-01-26
> 2006-01-27
> 2006-01-28
> 2006-01-29
> 2006-01-30
> 2006-01-31
>
> Like this. I would like to past in that sheet the amount of time in
> minutes that the thing have been open. Example : 2006-01-27 07:17:29
> 1
> 2006-01-30 20:41:35
> 0
> That mean's the thing have been open for 3 days, but i need a macro
> that will put for january 28, 1440 minutes, and january 29, 1440
> minutes, and all the minutes for the other days!! Which you guys
> understand me :P! Ask some clarifications if needed :P!
> Thanks you!
>
>
> --
> mhax
> ------------------------------------------------------------------------
> mhax's Profile: http://www.excelforum.com/member.php...o&userid=36450
> View this thread: http://www.excelforum.com/showthread...hreadid=562124
>
>

**mhax** · 07-17-2006, 04:54 PM

Well i got an objet that receive electricity. If it receive electricity, the objet will put a binairy number (1 in that case), the date and the time in memory. If the object doesnt receive electricity, it will put a 0 binairy, the date and the time in memory. After a couple of day i go take the values in the memory. It looks like this :

Date - Time - 0 or 1
Date format - 2006-01-01
Time format - 15:15:15

I need to calculate for how long the object received electricity per day!

Here an example :

2006-01-03 at 10:00 = 1
2006-01-03 at 12:00 = 0
2006-01-03 at 13:30 = 1
2006-01-03 at 20:00 = 0

so for the 2006-01-03 the object received a total of 510 minutes of electricity!

The problem is that the object can reveice electricity during night!
So sometimes i have something like this :
2006-01-04 at 20:30 = 1
2006-01-05 at 10:00 = 0

So in the interval there is 810 minutes, but there is a part for the 2006-01-04 and the 2006-01-05 : 210 minutes for the 4th and 600 for the 5th!

So i need a macro that could automaticly calculate this! Thanks you!

Wish it's clearer :P!

excelent · 07-17-2006, 05:20 PM

ok that help a'lot :-)

ill try find a solusion and return as fast i can

"mhax" skrev:

>
> Well i got an objet that receive electricity. If it receive electricity,
> the objet will put a binairy number (1 in that case), the date and the
> time in memory. If the object doesnt receive electricity, it will put a
> 0 binairy, the date and the time in memory. After a couple of day i go
> take the values in the memory. It looks like this :
>
> Date - Time - 0 or 1
> Date format - 2006-01-01
> Time format - 15:15:15
>
> I need to calculate for how long the object received electricity per
> day!
>
> Here an example :
>
> 2006-01-03 at 10:00 = 1
> 2006-01-03 at 12:00 = 0
> 2006-01-03 at 13:30 = 1
> 2006-01-03 at 20:00 = 0
>
> so for the 2006-01-03 the object received a total of 510 minutes of
> electricity!
>
> The problem is that the object can reveice electricity during night!
> So sometimes i have something like this :
> 2006-01-04 at 20:30 = 1
> 2006-01-05 at 10:00 = 0
>
> So in the interval there is 810 minutes, but there is a part for the
> 2006-01-04 and the 2006-01-05 : 210 minutes for the 4th and 600 for the
> 5th!
>
> So i need a macro that could automaticly calculate this! Thanks you!
>
> Wish it's clearer :P!
>
>
> --
> mhax
> ------------------------------------------------------------------------
> mhax's Profile: http://www.excelforum.com/member.php...o&userid=36450
> View this thread: http://www.excelforum.com/showthread...hreadid=562124
>
>

**mhax** · 07-17-2006, 10:20 PM

hehe that's cool

! Thanks you!

excelent · 07-18-2006, 07:15 AM

humm.. think we ned more experts to help
i surgest that u make a new/same question and ask som like this

Tis 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 ned:

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 ?