A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Count of cells since first purchase



 
 
Thread Tools Display Modes
  #1  
Old June 1st, 2010, 09:19 AM posted to microsoft.public.excel.misc
tonyagrey
external usenet poster
 
Posts: 10
Default Count of cells since first purchase

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
  #2  
Old June 1st, 2010, 04:20 PM posted to microsoft.public.excel.misc
tonyagrey
external usenet poster
 
Posts: 10
Default Count of cells since first purchase

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

  #3  
Old June 1st, 2010, 04:53 PM posted to microsoft.public.excel.misc
Tom-S[_2_]
external usenet poster
 
Posts: 68
Default Count of cells since first purchase

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

  #4  
Old June 2nd, 2010, 06:23 AM posted to microsoft.public.excel.misc
Tom-S[_2_]
external usenet poster
 
Posts: 68
Default Count of cells since first purchase

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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:47 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.