Using a Date Range

[Darin Spence]

I have a list of products, and the dates that they are on sale
(SHEET1):

PRODUCT START END
PROD1 1-5-04 1-18-04
PROD2 2-2-04 2-22-04
PROD3 2-2-04 2-22-04
PROD1 2-23-04 3-14-04
PROD1 3-15-04 3-28-04
PROD1 4-5-04 4-18-04

Some proucts run "on sale" during multiple periods.

I have a worksheet that lists every product (about 350 of them) in
rows of column A, then every WEEK in the adjacent columns i.e. B1,
then C1, D1, E1, F1, etc.). So, it looks like this (I'll explain the
"1's" below):

(SHEET2):
1 2 3 4 5 6 7 8 9 ... ...(weeks)
PROD1
PROD2 1 1 1
PROD3
PROD4
PROD5
PROD6
PROD7

I need to know if a product is on sale, and what week it is on sale by
looking at the above worksheet. So, based on the list, PROD2 is on
sale in weeks 5&6. I put a "1" in the cells where a product is on
sale, maybe there's a better way to do it. Ultimately, I'm just
wanting to have a number there so I can color it "white," then use
conditional formatting and turn the cell some color if it's greater
than 0."

Here are some secondary facts:
* The sales periods always start on a Monday, and end on a Sunday.
* Some proucts run "on sale" during multiple periods.
* I can't "buy" products on Sunday, so all "ending dates" should be
have 1 day subtracted from them.

Example: PROD2 (Above),

Sale Date Range: 2/2/04 - 2/22/04

Based on the list (SHEET1), PROD2 is "on-sale:" during the following
weeks:
6, 7 & 8, but NOT week 9 (because I can't buy on Sunday).

So, the question is, based on the List in SHEET1, can I create a sheet
similiar to SHEET2? There are over 1000 sales periods throughout the
year, so you can see why SHEET1 is difficult to manage, and something
like SHEET2 would be very cool.

Any ideas?

 

[keepITcool]

1 2
05-01 12-01 19-01 26-01 02-02 09-02
PROD1 1 1 0 0 0 0
PROD2 0 0 0 0 1 1
PROD3 0 0 0 0 1 1


define names for the columns in your data..


formula B3=
=--(SUMPRODUCT(--(PRODUCT=$A3),--(START<=B$2),--(END>=(B$2+6)))>0)

Please figure out something with the weeknumbers, and the dates..
these formulas need a date in row2
...which ofcourse could be hidden

To disaply some nice checkmark:

Format table with wingding font
Number format "ü";;

(its a u with double dot (enter as alt 0252) use numpad..


have fun!


keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[Terri Carlson]

Hi,

You need to do a "If / Then" function. Look under your
functions (see Excel help if you don't know what I am
talking about). Once you find the right formula
function - it should walk you through (by process of a
wizard).

Good luck.