# Counting and using dates

#### OrangeViking

##### New Member
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!!

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

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

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

##### MrExcel MVP
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
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
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é

Replies
1
Views
141
Replies
4
Views
270
Replies
6
Views
96
Replies
17
Views
338
Replies
5
Views
96