Outside dates and between dates

[Guest]

I am trying to find a method of isolating values that fall within a MONTH PERIOD starting of month B1 = 02/01/04 and B4 = 03/01/04 (one month = 43200 minutes), but at times activity begins inside of dates in B2 = 02/10/04 and B3 = 02/25/04. The values that occur between B1 and B2, between B3 and B4 I capture OK. However, activity that occurs between B2 and B3 is where I am having a bit of problem gathering the data values

Whatever happens bewteen B1 and B2, and B3 and B4 I tag as "NO ACTIVITY". Now, there can also periods of "NO ACTIVITY" taking place between B2 and B3.

Is there a method I can use to capture that data, then, combine the two values to total no more than 43200 minutes equal to one month

Any suggestions will be apprciated...........

 

[Frank Kabel]

Hi
not quite sure what you're trying to achieve. You may post some example
rows (plain text please) and define your expected results

--
Regards
Frank Kabel
Frankfurt, Germany

I am trying to find a method of isolating values that fall within a

MONTH PERIOD starting of month B1 = 02/01/04 and B4 = 03/01/04 (one
month = 43200 minutes), but at times activity begins inside of dates in
B2 = 02/10/04 and B3 = 02/25/04. The values that occur between B1 and
B2, between B3 and B4 I capture OK. However, activity that occurs
between B2 and B3 is where I am having a bit of problem gathering the
data values.

Whatever happens bewteen B1 and B2, and B3 and B4 I tag as "NO

ACTIVITY". Now, there can also periods of "NO ACTIVITY" taking place
between B2 and B3.

Is there a method I can use to capture that data, then, combine the

two values to total no more than 43200 minutes equal to one month?

 

[Rusty_nl]

I am not sure what you are trying to do.

But can't you use the MONTH() formula to determine the month number an
then use that to determine if there is activity in that month.

If that is not what you are trying to do could explain abit more detai
on what it is you are trying

 

[Guest]

Normally there is activity during the entire month = 43200 minutes. Beginning on the 02/01/04 - 03/01/04, but there are time when activity may not begin until some time later then 02/01/04 and this activity may days prior to the the end of month 03/01/04. See example B2 = 02/10/04 and the last activity may be on 02/25/04. But say that there was activity beginning on B2 = 02/10/04 which ran to 02/15/04, then nothing happening until 02/19/04 and continued to (B3) 02/25/04. That period between 02/15/04 and 02/19/04 will/should be listed as "NO ACTIVITY" even though this occurred between the start of the first (B2) Activity = 02/10/04and the Last (B3) activity = 02/25/04

This period of "NO AVTIVITY" occurring between 02/15/04 and 02/19/04 is the additional value I need to capture and add it to the "NO ACTIVITY" periods between to (B1) = 02/01/04 and (B2) = 02/10/04 together the "NO ACTIVITY" occurring between (B3) = 02/25/04 and the end of month (B4) = 03/01/04

Hope this helps you understand a bit better.

 

[Frank Kabel]

Hi
first: you can't assign a fixed value of 43200 minutes for each month
due to different number of days per month (you semm to calcualte fixed
30 days per month).

You may use the following formula to calculate the number of days which
arw active
=(B4-B3+1)+(B2-B1+1)
(this counts the days in B1:B4 also as active days)

To get the minutes use
=((B4-B3+1)+(B2-B1+1))*24*60

--
Regards
Frank Kabel
Frankfurt, Germany

Normally there is activity during the entire month = 43200 minutes.

Beginning on the 02/01/04 - 03/01/04, but there are time when activity
may not begin until some time later then 02/01/04 and this activity may
days prior to the the end of month 03/01/04. See example B2 = 02/10/04
and the last activity may be on 02/25/04. But say that there was
activity beginning on B2 = 02/10/04 which ran to 02/15/04, then nothing
happening until 02/19/04 and continued to (B3) 02/25/04. That period
between 02/15/04 and 02/19/04 will/should be listed as "NO ACTIVITY"
even though this occurred between the start of the first (B2) Activity
= 02/10/04and the Last (B3) activity = 02/25/04.

This period of "NO AVTIVITY" occurring between 02/15/04 and 02/19/04

is the additional value I need to capture and add it to the "NO
ACTIVITY" periods between to (B1) = 02/01/04 and (B2) = 02/10/04
together the "NO ACTIVITY" occurring between (B3) = 02/25/04 and the
end of month (B4) = 03/01/04.