excel 2003

[silentbuddha]

Hi,

I am stuck on this particular situation....

Example :

variable 1: Start date 9/26/2010
variable 2: end date 10/2/2010
constant : # days between startdate & enddate is always 7 days

How can I determine ( using the above date range as an example ) that for the current week I had 5 days that were in september and 2 days in october ???

Scenario : What I am trying to do is accurately calculate the CBC ( customer base calling ) for a certain week.


Formula:

step1
sept weighted days = 71.4 % ( 5 out of 7 )
oct weighted day = 28.6 % ( 2 out of 7 )

step2
71.4% * Total number of days in sept = 0.714 * 30 = 21.42
28.6% * total number of days in oct = 0.296 * 31 = 9.18

step3
weighted # days = 21.42 + 9.18 = 30.6

step4
( ( [calls offered] / [7 days] ) * weighted # days ) / subbase

Thanks !

 

[shg]

      --A--- --B-- --C-- --D--
  1    Beg    End  1-Sep 1-Oct
  2   26-Sep 2-Oct     5     2

The formula in C2 and copied across is

=MAX(0, MIN(EOMONTH(C$1, 0), $B2) - MAX(C$1, $A2) + 1)