Calculate Customers Duration and Break in Service 1

[BxWill]

Have 4 column worksheet in Excel 2007 with over 14,000 records that contain the Date spans for customers as displayed below.

AcctNo.---BeginDt----EndDt-----CustDuration1---CustDuration2
12345-----8/1/02-----8/31/03---1.0822----------
12345-----9/1/03-----5/31/04---0.7479----------
12345-----6/1/04-----7/31/04---0.1644----------
12345-----9/1/07-----12/31/11--2.4630----------

Currently calculating the duration (in terms of years) that the customer has been with us in the fourth column titled "CustDuration1" by using a IF statement like
"=IF(C3>DATE(2010,2,17),($G$1-F3)/365,(G3-F3)/365)". Note, cell $G$1 contains today's date.

The column titled "CustDuration2" should contain the length of time (in terms of years) that the customer was with us from 10/08 through 09/09. Of course, if the customer was with us for the full 12 months, this column should display "1".

The other column desired is "CustomerBreakInService" that should display the length of time (in terms of years) that the customer was not our customer from the time of the earliest "BeginDt" through the latest "EndDt."

Any insight as to how all of this can be accomplished in Excel? Also, is there a process to calculate the column titled "CustDuration1" instead of using IF statements?

Comtemplating the use of vba along with arrays but need additional insight.

 

[SkipVought]

Hi,

Your second duration is an AGGREGATION. So how do you want the data presented?

Using Named ranges in an ARRAY FORMULA...
[tt]
=IF(A2<>A1,(((AcctNo.=A2)*MAX(EndDt))-((AcctNo.=A2)*MIN(BeginDt)))/365,"")
[/tt]
the value I calculate is
[tt]
9.421917808

[/tt]
years

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[BxWill]

CustDuration2" should contain the length of time (in terms of years) that the customer was with us from 10/08 through 09/09.

I did perform the aggregation of all date spans using the pivot feature in Excel. However, I will try the array formula.

Also, would like to determine any break in service for all customers.

 

[SkipVought]

Also, would like to determine any break in service for all customers.

If SUM(CustDuration1)<SUM(CustDuration2) then "BREAK"

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[BxWill]

Not quite able to determine if the customers were with us for the entire 12 month period from 10/08 to 09/09 using the formulae.

Also, not able to calculate the number of months that the customer was not with us - the "Break" period.

Will try to solve manually.

 

[SkipVought]

If on each row, you calculate the duration, the sum of the durations is the total years engaged. If that value is less than the MAX(END)-MIN(BEGIN), they were not engaged the entire time, and consequently had at least ONE break in service.

To find a break
[tt]
F2: =IF(A2=A1,IF(B2-C1>1,B2-C1,""),"")
[/tt]
in break days.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[BxWill]

Will try this.

Conceptualizing regarding the determination of the number of months the customer has been with us during the date period Oct 2008 through Sept 2009;

For each account number, a comparison of each cell in columns B(BeginDt) should be made in reference to whether the value is before 10/01/2008 and then a comparison should be made to determine if the value in column C (EndDt) is after 9/30/2009 or not.

Then, maybe a computation of the duration in months could be made and displayed in column F.


Currently performing this step on a manual basis after sorting the data...