Prev: Split report for email
Next: Leading Zero as value not appearance
From: tonyagrey on 1 Jun 2010 04:19
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!

--
Tony
From: tonyagrey on 1 Jun 2010 11:20
Anyone?
--
Tony


"tonyagrey" wrote:

> 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 1May 10
> Account 1 2 3
> Account 2 1
> Account 3 1
>
> Any help would be greatly accepted!
>
> --
> Tony
From: Tom-S on 1 Jun 2010 11:53
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


"tonyagrey" wrote:

> Anyone?
> --
> Tony
>
>
> "tonyagrey" wrote:
>
> > 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 1May 10
> > Account 1 2 3
> > Account 2 1
> > Account 3 1
> >
> > Any help would be greatly accepted!
> >
> > --
> > Tony
From: Tom-S on 2 Jun 2010 01:23
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


"tonyagrey" wrote:

> Anyone?
> --
> Tony
>
>
> "tonyagrey" wrote:
>
> > 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 1May 10
> > Account 1 2 3
> > Account 2 1
> > Account 3 1
> >
> > Any help would be greatly accepted!
> >
> > --
> > Tony
 | 
Pages: 1
Prev: Split report for email
Next: Leading Zero as value not appearance