# 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

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

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

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,131
Messages
5,835,579
Members
430,368
Latest member
User800

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back