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 emailNext: Leading Zero as value not appearance