A possible formula or lookup table

tpsychi

New Member
Joined
Jan 23, 2009
Messages
9
Excel Workbook
ABCDE
1exam dateexam qtr10th qtr from exam date
24/1/2008Spring 2008Fall 201110/1/2008Fall 2008
31/1/2009Winter 2009
4Need a formula that will add 10 quarters to 4/1/084/1/2009Spring 2009
5or Spring 2008 and give us the result Fall 2011.10/1/2009Fall 2009
61/1/2010Winter 2010
74/1/2010Spring 2010
810/1/2010Fall 2010
91/1/2011Winter 2011
104/1/2011Spring 2011
1110/1/2011Fall 2011
121/1/2012Winter 2012
134/1/2012Spring 2012
1410/1/2012Fall 2012
Sheet1


Need help with how to find the 10th quarter for each exam date.


thanks

tpsychi
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Times New Roman,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 113px"><COL style="WIDTH: 113px"><COL style="WIDTH: 138px"><COL style="WIDTH: 113px"><COL style="WIDTH: 113px"><COL style="WIDTH: 71px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">Exam Date</TD><TD style="TEXT-ALIGN: center">Exam Qtr</TD><TD style="TEXT-ALIGN: center">10th Qtr from Exam Date</TD><TD style="TEXT-ALIGN: center">Exam Qtr</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">04/01/2008</TD><TD style="BACKGROUND-COLOR: #ffff00">Spring 2008</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">10/01/2011</TD><TD>Fall 2011</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">04/01/2008</TD><TD style="BACKGROUND-COLOR: #ffff00">Spring 2008</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">10/01/2008</TD><TD>Fall 2008</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: center">01/01/2009</TD><TD>Winter 2009</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: center">04/01/2009</TD><TD>Spring 2009</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: center">10/01/2009</TD><TD>Fall 2009</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: center">01/01/2010</TD><TD>Winter 2010</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: center">04/01/2010</TD><TD>Spring 2010</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: center">10/01/2010</TD><TD>Fall 2010</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: center">01/01/2011</TD><TD>Winter 2011</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: center">04/01/2011</TD><TD>Spring 2011</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: center">10/01/2011</TD><TD>Fall 2011</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: center">01/01/2012</TD><TD>Winter 2012</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: center">04/01/2012</TD><TD>Spring 2012</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: center">10/01/2012</TD><TD>Fall 2012</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: center">01/01/2013</TD><TD>Winter 2013</TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B2</TD><TD>=VLOOKUP(A2,$E$2:$F$16,2,FALSE)</TD></TR><TR><TD>C2</TD><TD>=INDEX(E2:E16,MATCH(A2,$E$2:$E$16,0)+10)</TD></TR><TR><TD>E2</TD><TD>=A2</TD></TR><TR><TD>F2</TD><TD>=LOOKUP(MONTH(E2),{1,4,7,10},{"Winter ","Spring ","Summer ","Fall "})&YEAR(E2)</TD></TR><TR><TD>E3</TD><TD>=DATE(YEAR(A2),MONTH(A2)+LOOKUP(MONTH(A2),{1,4,7},{3,6,3}),DAY(A2))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

I hope it works.
 
Last edited:
Upvote 0
if i add 10 quarters to 1 apr 2008 i do not get your result however i try, even counting on my fingers

here is a formula to return a date 10 quarters (10*3 months) from a cell date, you can build this into your existing formula
=(DATE(YEAR(A32)+(10*3)/12,MONTH(A32)+MOD(10*3,12),DAY(A32)))
 
Upvote 0
thanks for everyone's help i forgot to mention about skipping the summer quarter so sorry for some confusion.
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,072
Members
448,546
Latest member
KH Consulting

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top