Combined VLOOKUP AND BETWEEN FUNCTION

Hello,

I have a date in Column A
What I am trying to accomplish is assigning a week value to this in Column C
being 1-5, pending on the date. Column B I have the month assigned based of
the date for pivot table purposes. example below.

A1 = 10/01/2005
B1 = October
C1 = 1

C1 being formulated to pull of a predefined table or if anyone has a better
suggestion please help!

Thank you.
Lou

It depends upon how you determine the week number. For instance, does the
week always start at the first, if so, just use =INT((DAY(A1)+6)/7). If it
starts on a day of the week, say Sunday, you need something like

=INT((A1-IF(WEEKDAY(DATEVALUE("01-"&A2&YEAR(TODAY())))=1,DATEVALUE("01-"&A2&
YEAR(TODAY())),DATEVALUE("01-"&A2&YEAR(TODAY()))-WEEKDAY(DATEVALUE("01-"&A2&
YEAR(TODAY())),2))+7)/7)

--
HTH

Bob Phillips

"Louis Markowski" <[email protected]> wrote in
message news:[email protected]...
> Hello,
>
> I have a date in Column A
> What I am trying to accomplish is assigning a week value to this in Column
C
> being 1-5, pending on the date. Column B I have the month assigned based
of
> the date for pivot table purposes. example below.
>
> A1 = 10/01/2005
> B1 = October
> C1 = 1
>
> C1 being formulated to pull of a predefined table or if anyone has a
better
> suggestion please help!
>
> Thank you.
> Lou

You can use the following formula to get the date from the cell A1
=TEXT(A1,"d")
Alok

"Louis Markowski" wrote:

> Hello,
>
> I have a date in Column A
> What I am trying to accomplish is assigning a week value to this in Column C
> being 1-5, pending on the date. Column B I have the month assigned based of
> the date for pivot table purposes. example below.
>
> A1 = 10/01/2005
> B1 = October
> C1 = 1
>
> C1 being formulated to pull of a predefined table or if anyone has a better
> suggestion please help!
>
> Thank you.
> Lou

Sorry. Did not read the post - thought OP wanted the formula for the day from
the date!

Alok

"Alok" wrote:

> You can use the following formula to get the date from the cell A1
> =TEXT(A1,"d")
> Alok
>
> "Louis Markowski" wrote:
>
> > Hello,
> >
> > I have a date in Column A
> > What I am trying to accomplish is assigning a week value to this in Column C
> > being 1-5, pending on the date. Column B I have the month assigned based of
> > the date for pivot table purposes. example below.
> >
> > A1 = 10/01/2005
> > B1 = October
> > C1 = 1
> >
> > C1 being formulated to pull of a predefined table or if anyone has a better
> > suggestion please help!
> >
> > Thank you.
> > Lou