Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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" wrote in message ... 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 |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|