Count of cells since first purchase

[tonyagrey]

Hi,

Looking for a bit of help with the below.

I want to calculate how many weeks have passed since a customer first
purchased from me. So for example see below. I want to calculate in cell E2
how many weeks have passed since Account 1 first purchased from me. In cell
E3 i want to calculate how many weeks it is since Account 2 first purchased
from me.


Week 3 April Week 4 April Week 1
May 10
Account 1 2
3
Account 2 1
Account 3 1

Any help would be greatly accepted!

 

[tonyagrey]

Anyone?

 

[Tom-S]

Say you set up your worksheet something like this:

A2 holds "Account 1", B2 to D2 holds the year, month (as a number), and day
number that Account 1 first purchased from you e.g. if first purchase was 27
May 2008, then B2 holds 2008, C2 holds 5, and D2 holds 27.

In E1 is a column header, "No. weeks since first purchase", and in E2 is the
formula

=WEEKNUM(NOW(),1)-WEEKNUM(DATE(B2,C2,D2),1)

Note that the '1' in the WEEKNUM function assumes your week begins on Sunday
- if you want week beginning Monday change this to '2' i.e.

=WEEKNUM(NOW(),2)-WEEKNUM(DATE(B2,C2,D2),2)

Hope that helps with what you need.

Regards,

Tom

 

[Tom-S]

Sorry Tony, I realised on reflection that the formula I gave in my first
reply would only work if the purchase year and current year were the same. To
be able to count the weeks across any number of years try the following
instead:

Say you have column headers in A1 to C1: Account No., Date of 1st Purchase,
No. Weeks Since 1st Purchase

Then in A2 type 1, in B2 (which is formatted as a date) the date of Account
1's first purchase, then in C2 (which is formatted as a number to 1 d.p.)
this formula:

=DATEDIF(B2,TODAY(),"D")/7

The DATEDIF function will calculate the number of days between TODAY's date
and the date entered in B2, and the formula converts to weeks by dividing by
7.

Hope this is more in line with what you need.

Regards,

Tom