Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 -- mazalam ------------------------------------------------------------------------ mazalam's Profile: http://www.excelforum.com/member.php...o&userid=26768 View this thread: http://www.excelforum.com/showthread...hreadid=468810 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 -- mazalam ------------------------------------------------------------------------ mazalam's Profile: http://www.excelforum.com/member.php...o&userid=26768 View this thread: http://www.excelforum.com/showthread...hreadid=468810 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 |
#9
|
|||
|
|||
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 |
#10
|
|||
|
|||
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 |
#11
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I NEED HELP with the SPELLNUMBER Function | Excel Worksheet Functions | |||
Excel option to store trendline's coefficients in cells for use | Charts and Charting in Excel | |||
Date & Time | New Users to Excel | |||
Automatically up date time in a cell | Excel Discussion (Misc queries) | |||
clock | Excel Worksheet Functions |