if function
 

I have a column A with 1000 different dates. I want excel to look up the
dates and if it falls under a specific date range, label column "B"s
respective cells a certain Fiscal week.

For example:

1/31/2005 Q1,WK1
2/1/2005 Q1,Wk1
2/2/2005 Q1,Wk1
2/3/2005 Q1,Wk1
2/4/2005 Q1,Wk1
2/7/2005 Q1,Wk2
2/8/2005 Q1,Wk2
2/9/2005 Q1,Wk2
2/10/2005 Q1,Wk2
2/11/2005 Q1,Wk2 :cool:


--
mazalam
------------------------------------------------------------------------
mazalam's Profile: http://www.excelforum.com/member.php...o&userid=26768
View this thread: http://www.excelforum.com/showthread...hreadid=468810

How do you come up with 1/31/2005 Q1,WK1? Are you wanting the week of
the month or the week of the Qtr?


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=468810

The quarters are easy. If the date is in A1 then:

=CHOOSE(CEILING(MONTH(A1)/3,1),"Q1","Q2","Q3","Q4")

will give the correct quarter. I don't understand your week number.
Please update the post.
--
Gary''s Student


"mazalam" wrote:


I have a column A with 1000 different dates. I want excel to look up the
dates and if it falls under a specific date range, label column "B"s
respective cells a certain Fiscal week.

For example:

1/31/2005 Q1,WK1
2/1/2005 Q1,Wk1
2/2/2005 Q1,Wk1
2/3/2005 Q1,Wk1
2/4/2005 Q1,Wk1
2/7/2005 Q1,Wk2
2/8/2005 Q1,Wk2
2/9/2005 Q1,Wk2
2/10/2005 Q1,Wk2
2/11/2005 Q1,Wk2 :cool:


--
mazalam
------------------------------------------------------------------------
mazalam's Profile: http://www.excelforum.com/member.php...o&userid=26768
View this thread: http://www.excelforum.com/showthread...hreadid=468810

swatsp0p Wrote:
How do you come up with 1/31/2005 Q1,WK1? Are you wanting the week of
the month or the week of the Qtr?


I want it week of the quarter.


--
mazalam
------------------------------------------------------------------------
mazalam's Profile: http://www.excelforum.com/member.php...o&userid=26768
View this thread: http://www.excelforum.com/showthread...hreadid=468810

Then try this...assumes dates begin in A2, paste in B2, copy down 1k
rows:

=IF(WEEKNUM(A2,1)52,"Qtr: 4 Week: 14","Qtr
"&IF(MOD(WEEKNUM(A2,1),13)=0,WEEKNUM(A2,1)/13,INT(WEEKNUM(A2,1)/13)+1)&"
Week: "&IF(MOD(WEEKNUM(A2,1),13)=0,13,MOD(WEEKNUM(A2,1), 13)))

Things get really ugly late in December, as often there are more that
13 weeks in the 4th Qtr. This attempts to address that by making a
week 14 in Qtr 4.

I hope this meets your needs.

note: you need to have the Analysis ToolPak Add-In installed for the
"WEEKNUM" function to work.

Bruce


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=468810

if function
 

Hi,

I've been looking for this for a while, thank-you! Could yo explain how it
works please/ I'm not familar with the CHOOSE and CEILING functions.

Matt

"Gary''s Student" wrote:

The quarters are easy. If the date is in A1 then:

=CHOOSE(CEILING(MONTH(A1)/3,1),"Q1","Q2","Q3","Q4")

will give the correct quarter.

Gary''s Student

if function
 

The MONTH(A1)/3 takes the month of the given date, and divides by to

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Matt D Francis" wrote in message
...
Hi,

I've been looking for this for a while, thank-you! Could yo explain how it
works please/ I'm not familar with the CHOOSE and CEILING functions.

Matt

"Gary''s Student" wrote:

The quarters are easy. If the date is in A1 then:

=CHOOSE(CEILING(MONTH(A1)/3,1),"Q1","Q2","Q3","Q4")

will give the correct quarter.

Gary''s Student

if function
 

Sorry, hit the button too soon.

The MONTH(A1)/3 takes the month of the given date, and divides by 3 to give
a value of .33,.66 or 1 for the first three months, 1.33, 1.66 or 2 for the
second 3 months, etc.

CEILING(val,1) takes it up to the nearest integer, 1,2, etc.

CHOOSE then uses that value to pick from the comma delimited list.

Example,

A1 contains 20th October

MONTH(A1) = 10
MONTH(A1)/3 = 3.33
CEILING(MONTH(A1)/3),1) = 4
CHOOSE then picks the 4th value, Q4

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Matt D Francis" wrote in message
...
Hi,

I've been looking for this for a while, thank-you! Could yo explain how it
works please/ I'm not familar with the CHOOSE and CEILING functions.

Matt

"Gary''s Student" wrote:

The quarters are easy. If the date is in A1 then:

=CHOOSE(CEILING(MONTH(A1)/3,1),"Q1","Q2","Q3","Q4")

will give the correct quarter.

Gary''s Student

if function
 

For quarters this will also work;

="Q"&LOOKUP(MONTH(A1),{0,1;4,2;7,3;10,4})

HTH


--
Krishnakumar


------------------------------------------------------------------------
Krishnakumar's Profile: http://www.excelforum.com/member.php...o&userid=20138
View this thread: http://www.excelforum.com/showthread...hreadid=468810

if function
 

He already had a solution that worked, he just wanted to understand it!

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Krishnakumar"
wrote in message
news:Krishnakumar.1x8suf_1129881915.3285@excelforu m-nospam.com...

For quarters this will also work;

="Q"&LOOKUP(MONTH(A1),{0,1;4,2;7,3;10,4})

HTH


--
Krishnakumar


------------------------------------------------------------------------
Krishnakumar's Profile:
http://www.excelforum.com/member.php...o&userid=20138
View this thread: http://www.excelforum.com/showthread...hreadid=468810

if function
 

Brilliant, if I actually understand the formula, I can see where else the
functions could be used. Thank-you.

Matt

"Bob Phillips" wrote:

Sorry, hit the button too soon.

The MONTH(A1)/3 takes the month of the given date, and divides by 3 to give
a value of .33,.66 or 1 for the first three months, 1.33, 1.66 or 2 for the
second 3 months, etc.

CEILING(val,1) takes it up to the nearest integer, 1,2, etc.

CHOOSE then uses that value to pick from the comma delimited list.

Example,

A1 contains 20th October

MONTH(A1) = 10
MONTH(A1)/3 = 3.33
CEILING(MONTH(A1)/3),1) = 4
CHOOSE then picks the 4th value, Q4


HTH

RP