Counting and using dates

[OrangeViking]

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!!

 

[fairwinds]

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]

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
	A	B	C	D	E
1	Start	Stop		Interval
2	2005-12-20 22:35	2005-12-21 04:55	IN	23:00
3	2005-12-20 23:15	2005-12-21 04:55	OUT	01:00
4
Sheet1

 

[OrangeViking]

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]

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

 

[fairwinds]

Before your last post I came up with:

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

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]

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é