Weeks and days sorting out

[Guest]

Hi there I hope someone could help me

I've got a column of dates in column A and the sales for each date in column B

What I need to do is that I have to classify these daily sales figures into
weekly sales figures downloaded onto my "Sales!" sheet
Then I've created a new sheet "Sheet1!"and put the to and from date in
column A and B and the week number in column C

For example
Date From Date To: Week Number:
01/01/2006 07/01/2006 1
08/01/2006 15/01/2006 2

I would like to do a lookup in my downloaded daily sales figures and I've
put a formula:

If(Sales!A1<Sheet1!B2,if(Sales!A1>Sheet!A1,vlookup(Sales
A1,Sheet!A:C,3,0),"Checking")

However it doesnt work, can somebody please help me and tell me what have I
done wrong and how should I put it right please?

Thanks a lot

Vivi

 

[smw226 via OfficeKB.com]

Hi Vivi,

You can calculate the week a date is in by using the following:

=ROUNDUP(( [1] - [2] )/7,0)

where:
1-Your date
2-The numerical value for the 01/01 of the year you are calculating (38718
for 2006)

If you are aiming to report on weekly sales then this should do you just fine.


HTH

Thanks,

Simon

Hi there I hope someone could help me

I've got a column of dates in column A and the sales for each date in column B

What I need to do is that I have to classify these daily sales figures into
weekly sales figures downloaded onto my "Sales!" sheet
Then I've created a new sheet "Sheet1!"and put the to and from date in
column A and B and the week number in column C

For example
Date From Date To: Week Number:
01/01/2006 07/01/2006 1
08/01/2006 15/01/2006 2

I would like to do a lookup in my downloaded daily sales figures and I've
put a formula:

If(Sales!A1<Sheet1!B2,if(Sales!A1>Sheet!A1,vlookup(Sales
A1,Sheet!A:C,3,0),"Checking")

However it doesnt work, can somebody please help me and tell me what have I
done wrong and how should I put it right please?

Thanks a lot

Vivi

--
--------------------
Simon - UK

Email at simon22mports [ a t ] hot mail [ d ot ]com

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/ms-excel/200610/1

 

[Bob Phillips]

=SUMPRODUCT(--(Sales!A1:A1000>=A2),--(Sales!A1:A1000<=B2),Sales!B1:B1000)


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

Hi

thanks for your post and your suggestions but unfortunately I've tried it
but it doesn't work for me, maybe I've haven't explained myself correctly

The first sheet contained a daily sales figure from 01/01/06 to 30/09/06

E.G.

01/01/06 £500
02/01/06 £600
03/01/06 £400
04/01/06 £600
05/01/06 £700
06/01/06 £500
07/01/06 £400

What I want to do is that from my lookup table in the 2nd Sheet, I have this
figures:

To From Week No.
31/12/2005 06/01/2006 1
07/01/2006 13/01/2006 2
14/01/2006 20/01/2006 3
21/01/2006 27/01/2006 4
28/01/2006 03/02/2006 5

So on my first sheet I would like to have a column to show me which number
of week does a day falls into e.g.
Date: £: Week no.
01/01/06 £500 1
02/01/06 £600 1
03/01/06 £400 1
04/01/06 £600 1
05/01/06 £700 1
06/01/06 £500 1
07/01/06 £400 1
08/01/06 £500 2

I've used your formula but I could onlu get an answer to the first cell but
then all the others are incorrectly showing zeros, is there other formulas
that I can use?

Thanks a lot

Vivi

 

[Bob Phillips]

=VLOOKUP(Sheet2!A1,Sheet3!$A$2:$C$30,3,TRUE)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[smw226 via OfficeKB.com]

Hi,

Ok...try this.

On sheet one paste the following in a spare column (change A1 to the cell
which containts the date):

=ROUNDUP((A1-38717)/7,0)

Then, on sheet 2 paste the same and change A1 to the START date of the week
in your list.

then in a spare column on sheet2 paste the following (change "Sheet1!B1:B10"
to the cells which contain the calculated week numbers on the first sheet ,
"sheet2!b1" to the cell which contains week number on the second sheet,
"Sheet1!C1:C10" to the dails sales figures on the first sheet"):

=SUMIF(Sheet1!$B$1:$B$10,"="&Sheet2!B1,Sheet1!$C$1:$C$10)


A more efficient way, however, would be to calculate the week on sheet 1 the
create a pivot table for the sum by week calculation.

Thanks,

Simon

Hi

thanks for your post and your suggestions but unfortunately I've tried it
but it doesn't work for me, maybe I've haven't explained myself correctly

The first sheet contained a daily sales figure from 01/01/06 to 30/09/06

E.G.

01/01/06 £500
02/01/06 £600
03/01/06 £400
04/01/06 £600
05/01/06 £700
06/01/06 £500
07/01/06 £400

What I want to do is that from my lookup table in the 2nd Sheet, I have this
figures:

To From Week No.
31/12/2005 06/01/2006 1
07/01/2006 13/01/2006 2
14/01/2006 20/01/2006 3
21/01/2006 27/01/2006 4
28/01/2006 03/02/2006 5

So on my first sheet I would like to have a column to show me which number
of week does a day falls into e.g.
Date: £: Week no.
01/01/06 £500 1
02/01/06 £600 1
03/01/06 £400 1
04/01/06 £600 1
05/01/06 £700 1
06/01/06 £500 1
07/01/06 £400 1
08/01/06 £500 2

I've used your formula but I could onlu get an answer to the first cell but
then all the others are incorrectly showing zeros, is there other formulas
that I can use?

Thanks a lot

Vivi

=SUMPRODUCT(--(Sales!A1:A1000>=A2),--(Sales!A1:A1000<=B2),Sales!B1:B1000)

[quoted text clipped - 27 lines]