Week Quarter Year - Custom Week

[footballdj123]

Hello,

I want to be able to look at a date and then return a Week Quarter and Year. The custom week goes from Thurs - Weds.

There is a standard return of week number and quarter but I can't figure out how to change that for custom week.

So far I am just doing a lookup to a custom file.

<style><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}@page {margin:.75in .7in .75in .7in; mso-header-margin:.3in; mso-footer-margin:.3in;}tr {mso-height-source:auto;}col {mso-width-source:auto;}br {mso-data-placement:same-cell;}td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;}.xl65 {color:windowtext; font-size:10.0pt; font-family:Arial, sans-serif; mso-font-charset:0; mso-number-format:"Short Date";}.xl66 {color:windowtext; font-size:10.0pt; font-family:Arial, sans-serif; mso-font-charset:0;}--></style>[TABLE="width: 261"]
<!--StartFragment--> <colgroup><col width="87" span="3" style="width:65pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 87, align: right"]1/1/18[/TD]
[TD="class: xl66, width: 87"]Week 1[/TD]
[TD="width: 87"]Q1 2018[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]1/2/18[/TD]
[TD]Week 1[/TD]
[TD]Q1 2018[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]1/3/18[/TD]
[TD]Week 1[/TD]
[TD]Q1 2018[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]1/4/18[/TD]
[TD]Week 1[/TD]
[TD]Q1 2018[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]1/5/18[/TD]
[TD="class: xl66"]Week 2[/TD]
[TD]Q1 2018[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]1/6/18[/TD]
[TD="class: xl66"]Week 2[/TD]
[TD]Q1 2018[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]1/7/18[/TD]
[TD="class: xl66"]Week 2[/TD]
[TD]Q1 2018[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]1/8/18[/TD]
[TD="class: xl66"]Week 2[/TD]
[TD]Q1 2018[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]1/9/18[/TD]
[TD="class: xl66"]Week 2[/TD]
[TD]Q1 2018[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]1/10/18[/TD]
[TD="class: xl66"]Week 2[/TD]
[TD]Q1 2018[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]1/11/18[/TD]
[TD="class: xl66"]Week 2[/TD]
[TD]Q1 2018[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]1/12/18[/TD]
[TD="class: xl66"]Week 3[/TD]
[TD]Q1 2018[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]1/13/18[/TD]
[TD="class: xl66"]Week 3[/TD]
[TD]Q1 2018[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]1/14/18[/TD]
[TD="class: xl66"]Week 3[/TD]
[TD]Q1 2018[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]1/15/18[/TD]
[TD="class: xl66"]Week 3[/TD]
[TD]Q1 2018[/TD]
[/TR]
<!--EndFragment--></tbody>[/TABLE]

 

[jtakw]

Hi,

I'm not sure if I understand what you're asking, are you looking for a formula to give you the Week Number based on your Custom Week?
If you are, your description says Thurs - Wed, but your sample shows Friday - Thurs, below is formula based on your sample:

Excel 2010
	A	B	C
1	Date	Week #	Quarter
2	1/1/2018	1	Q1 2018
3	1/2/2018	1	Q1 2018
4	1/3/2018	1	Q1 2018
5	1/4/2018	1	Q1 2018
6	1/5/2018	2	Q1 2018
7	1/6/2018	2	Q1 2018
8	1/7/2018	2	Q1 2018
9	1/8/2018	2	Q1 2018
10	1/9/2018	2	Q1 2018
11	1/10/2018	2	Q1 2018
12	1/11/2018	2	Q1 2018
13	1/12/2018	3	Q1 2018
14	1/13/2018	3	Q1 2018
15	1/14/2018	3	Q1 2018
16	1/15/2018	3	Q1 2018
Sheet11

Cell Formulas
Range	Formula
B2	=WEEKNUM(A2,15)

B2 formula copied down.

 

[jtakw]

If you Also need a formula for the Quarter/Year Column as shown in your sample, again week starts on Fridays:

Excel 2010
	A	B	C
1	Date	Week #	Quarter/Year
2	1/1/2018	1	Q1 2018
3	1/2/2018	1	Q1 2018
4	1/3/2018	1	Q1 2018
5	1/4/2018	1	Q1 2018
6	1/5/2018	2	Q1 2018
7	1/6/2018	2	Q1 2018
8	1/7/2018	2	Q1 2018
9	1/8/2018	2	Q1 2018
10	1/9/2018	2	Q1 2018
11	1/10/2018	2	Q1 2018
12	1/11/2018	2	Q1 2018
13	1/12/2018	3	Q1 2018
14	1/13/2018	3	Q1 2018
15	1/14/2018	3	Q1 2018
16	1/15/2018	3	Q1 2018
17	3/25/2018	13	Q1 2018
18	4/15/2018	16	Q2 2018
19	7/26/2018	30	Q3 2018
20	10/21/2018	43	Q4 2018
Sheet11

Cell Formulas
Range	Formula
B2	=WEEKNUM(A2,15)
C2	=LOOKUP(B2,{1,14,27,40},{"Q1","Q2","Q3","Q4"})&" "&YEAR(A2)

B2 and C2 formulas copied down.

 

[footballdj123]

Thanks and if I wanted to change it to Thursday - Weds, how would I go about doing that?

 

[jtakw]

Thanks and if I wanted to change it to Thursday - Weds, how would I go about doing that?

Change the B2 formula from 15 to 14:

Book1
	A	B	C
1	Date	Week #	Quarter/Year
2	1/1/2018	1	Q1 2018
3	1/2/2018	1	Q1 2018
4	1/3/2018	1	Q1 2018
5	1/4/2018	2	Q1 2018
6	1/5/2018	2	Q1 2018
7	1/6/2018	2	Q1 2018
8	1/7/2018	2	Q1 2018
9	1/8/2018	2	Q1 2018
10	1/9/2018	2	Q1 2018
11	1/10/2018	2	Q1 2018
12	1/11/2018	3	Q1 2018
13	1/12/2018	3	Q1 2018
14	1/13/2018	3	Q1 2018
15	1/14/2018	3	Q1 2018
16	1/15/2018	3	Q1 2018
17	3/25/2018	13	Q1 2018
18	4/15/2018	16	Q2 2018
19	7/26/2018	31	Q3 2018
20	10/21/2018	43	Q4 2018
21	12/31/2018	53	Q4 2018
Sheet11

Cell Formulas
Range	Formula
B2	=WEEKNUM(A2,14)
C2	=LOOKUP(B2,{1,14,27,40},{"Q1","Q2","Q3","Q4"})&" "&YEAR(A2)