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

Excel Facts

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

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é
 
Master Excel Bundle

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.

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top