Counting and using dates

OrangeViking

New Member
Joined
Nov 25, 2005
Messages
13
I have two columns. One with a start time and one with a stop time of an activity. Both columns have the format YYYY-mm-dd hh:mm

Now I want to count the activities that were active during a specific time interval, for example between 8:00 and 9:00 but not depending on the date.

An additional challenge occurs when the activity started the previous day and finish the next.

For example.
Start 2005-12-20 22:35
Stop 2005-12-21 04:55

Now I want to count the activities that took place between 02:00 and 03:00 irrelevant of the date.

Thanks for you help!!
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
Hi,

Does the interval have to be completely within the time?

E.g. If you were to count the interval 21:00 - 22:00 would your sample data give a count?
 

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
Anyway, if your answer is yes, try:

=IF(((A2<=INT(A2)+$D$2)+(A2<=INT(B2)+$D$2)*(B2>=INT(B2)+$D$2))*((B2>=INT(B2)+$D$3)+(B2>=INT(A2)+$D$3)*(A2<=INT(A2)+$D$3)),"IN","OUT")
Book2
ABCDE
1StartStopInterval
22005-12-20 22:352005-12-21 04:55IN23:00
32005-12-20 23:152005-12-21 04:55OUT01:00
4
Sheet1
 

OrangeViking

New Member
Joined
Nov 25, 2005
Messages
13
Tack fairwinds !! That works to test if the interval completely covers the time range.

Since I have about 10.000 rows of data I want to count them as well. Using your solution I will have to create a lot more columns and dump out a 0 or a 1 in each of the columns depending on if it is in the time range.

Can I check for a time range as described earlier and count at the same time ??

PS If the activity covers part of the time range it should be counted.
 

OrangeViking

New Member
Joined
Nov 25, 2005
Messages
13

ADVERTISEMENT

Just to complicate matters: if the activity continues for several days it should be counted for every day it falls in the interval

Start 2005-12-10 06:45
Stop 2005-12-12 09:13

Range 6:00 - 7:00

So in the example above the count should say 3 (10, 11, 12 of December).

I feel a nasty marco with loops coming up .... :confused:
 

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
Before your last post I came up with:

Code:
=SUMPRODUCT(--(((INT(A2:A10)+D3>A2:A10)*(INT(A2:A10)+D3<B2:B10)+(INT(B2:B10)+D3>A2:A10)*(INT(B2:B10)+D3<B2:B10)+(INT(A2:A10)+D2<B2:B10)*(INT(A2:A10)+D2>A2:A10)+(INT(B2:B10)+D2<B2:B10)*(INT(B2:B10)+D2>A2:A10))>0))

Now I have to think a little bit more.
 

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
Sorry, no good sollution.

You could use:

=SUMPRODUCT(--(MOD(ROW(INDIRECT(ROUND(MOD(A2,1)*1440,0)&":"&ROUND((INT(B2)-INT(A2)+MOD(B2,1))*1440,0)))/1440,1)=$D$2))+($D$2<MOD(A2,1))*((D3>D2)+($D$3>MOD(A2,1)>0))

in C2, dragged down and then summarized but it would most certainly make your sheet choke long before 10k rows.
 

OrangeViking

New Member
Joined
Nov 25, 2005
Messages
13
Fairwinds, thanks for your awesome help !! There are not many occurences where the activity lasts more than one day.

I am a newbie to VBA, but this might be a good time for me learn a bit more if I want to be 100% perfect.

Hejdå,
André
 

Watch MrExcel Video

Forum statistics

Threads
1,118,084
Messages
5,570,128
Members
412,305
Latest member
Mozz
Top